求和值不正确

debugcn 发表于 Dev

卡洛斯80

我正在尝试将表联接到映射表上。映射表中包含地理数据，其想法是首先查看最详细的信息，即邮政编码和mapp，如果未提供邮政编码，则将查看区域：

ID  Region  Country Description Zone    County      City            Postal_Code
9324    Australasia Australia   Tasmania    70      Western Shore   7000
9325    Australasia Australia   Tasmania    70      Western Shore   7004
9326    Australasia Australia   Tasmania    70      Western Shore   7005
9327    Australasia Australia   Tasmania    70      Western Shore   7007
9328    Australasia Australia   Tasmania    70      Western Shore   7008
9329    Australasia Australia   Tasmania    70      Western Shore   7009
9330    Australasia Australia   Tasmania    70      Western Shore   7010
9331    Australasia Australia   Tasmania    70      Western Shore   7011
9332    Australasia Australia   Tasmania    70      Western Shore   7012
9333    Australasia Australia   Tasmania    70      Eastern Shore   7015
9334    Australasia Australia   Tasmania    70      Eastern Shore   7016
9335    Australasia Australia   Tasmania    70      Western Shore   7017
9336    Australasia Australia   Tasmania    70      Eastern Shore   7018
9337    Australasia Australia   Tasmania    70      Eastern Shore   7019
9338    Australasia Australia   Tasmania    70      Eastern Shore   7020
9339    Australasia Australia   Tasmania    70      Eastern Shore   7021
9340    Australasia Australia   Tasmania    70      Eastern Shore   7022
9341    Australasia Australia   Tasmania    70      Eastern Shore   7023
9342    Australasia Australia   Tasmania    70      Eastern Shore   7024
9343    Australasia Australia   Tasmania    70      Sorell          7025
9344    Australasia Australia   Tasmania    70      Sorell          7026
9345    Australasia Australia   Tasmania    70      Sorell          7027
9346    Australasia Australia   Tasmania    70      Western Shore   7030
9347    Australasia Australia   Tasmania    70      Kingston        7050
9348    Australasia Australia   Tasmania    70      Kingston        7052
9349    Australasia Australia   Tasmania    70      Kingston        7053
9350    Australasia Australia   Tasmania    70      Kingston        7054
9351    Australasia Australia   Tasmania    70      Kingston        7055

这个想法是，所有内容都返回到“区域”和“描述”，但是联接取决于所提供的数据，因此我要格式化的数据是：

COUNTRY STATE   Zone        ZIP     TIV
Australia   NULL    70      NULL    1000000
Australia   NULL    70      NULL    1000000
Australia   NULL    70      NULL    1000000
Australia   NULL    70      NULL    1000000
Australia   NULL    NULL    7023    100000

所以答案看起来像这样：

COUNTRY Zone    NAME    Value
Australia   70  Tasmania    4000000

但是我有的代码是：

SELECT  GR.COUNTRY,
        GR.ZONE,
        GR.Zone_Desc AS NAME,
        sum(TA.TIV)

FROM [dbo].[Tmp_Format] TA

INNER JOIN [dbo].[GeoRes_Mapping] GR ON TA.COUNTRY = GR.Country AND TA.Zone = GR.Zone

WHERE TA.COUNTRY = 'Australia'

GROUP BY    GR.COUNTRY,
            GR.ZONE,
            GR.Zone_Desc,
            TA.TIV

我实际上得到的是：

COUNTRY  ZONE   NAME    Value
Australia   70  Tasmania    112000000

似乎是将4100000乘以映射表中的28个不同区域？道歉这么长的时间，但我真的很努力

戈登·利诺夫（Gordon Linoff）

它不是乘以不同的区域，而是乘以邮政编码。每个区域中有多行。也许这可以解决您的问题：

SELECT  GR.COUNTRY, GR.ZONE, GR.Zone_Desc AS NAME, sum(TA.TIV)
FROM [dbo].[Tmp_Format] TA INNER JOIN
     (select distinct country, zone, zone_desc
      from [dbo].[GeoRes_Mapping]
     ) GR
     ON TA.COUNTRY = GR.Country AND TA.Zone = GR.Zone
WHERE TA.COUNTRY = 'Australia'
GROUP BY GR.COUNTRY, GR.ZONE, GR.Zone_Desc;

另外，TIV也不应该在中group by。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。