【SQL】统计数据过程中利用Union去重

明细数据

--明细数据
SELECT AssetCard.cardid AS CardID
      ,AssetProp.Id AS idAssetProp
      ,AssetProp.Name AS AssetPropName
      ,AssetCard.origValue AS OrigValue
      ,AssetCard.totalDepr AS TotalDepr
      ,AssetCard.netamount AS NetAmount
      ,Asset.monthDeprAmount AS MonthDeprAmount
FROM AM_Asset AS AssetCard
    LEFT JOIN AA_AssetProp AS AssetProp ON AssetCard.idAssetProp = AssetProp.id
    INNER JOIN AM_Asset AS Asset ON Asset.cardid = AssetCard.cardid AND AssetCard.isActive = 1
WHERE 1 = 1 AND (Asset.srcvoucherdate >= '2012-05-01' AND Asset.srcvoucherdate < dateadd(DAY,1,'2012-06-30')) AND Asset.idprocesstype = 'AF3B75A8-CB29-43C6-B829-1F4A475347C5' 

上述查询的结果如下：

CardID	idAssetProp	AssetPropName	OrigValue	TotalDepr	NetAmount	MonthDeprAmount
C7B6879C-7F88-47F8-BD81-767D5557DC70	60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	848.75	2151.25	121.25
C7B6879C-7F88-47F8-BD81-767D5557DC70	60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	848.75	2151.25	121.25
8C6B1101-7665-439F-B7D0-4C55ADCD1A8A	60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	24.26	2975.74	12.13
8C6B1101-7665-439F-B7D0-4C55ADCD1A8A	60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	24.26	2975.74	12.13
612D3363-0FDE-4140-AF31-5C059D5978D0	60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	24.26	2975.74	12.13
612D3363-0FDE-4140-AF31-5C059D5978D0	60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	24.26	2975.74	12.13

如果按照idAssetProp、AssetPropName分组对查询结果进行统计，其中明细数据中的CardID，OrigValue、TotalDepr、NetAmount只能统计一遍，而相同分组的MonthDeprAmount直接求和。

初次处理结果

第一次查询的SQL如下：

SELECT AssetProp.Id AS idAssetProp
      ,AssetProp.Name AS AssetPropName
      ,sum(AssetCard.origValue) AS OrigValue
      ,sum(AssetCard.totalDepr) AS TotalDepr
      ,sum(AssetCard.netamount) AS NetAmount
      ,sum(Asset.monthDeprAmount) AS MonthDeprAmount
FROM AM_Asset AS AssetCard
    LEFT JOIN AA_AssetProp AS AssetProp ON AssetCard.idAssetProp = AssetProp.id
    INNER JOIN AM_Asset AS Asset ON Asset.cardid = AssetCard.cardid AND AssetCard.isActive = 1
WHERE 1 = 1 AND (Asset.srcvoucherdate >= '2012-05-01' AND Asset.srcvoucherdate < dateadd(DAY,1,'2012-06-30')) AND Asset.idprocesstype = 'AF3B75A8-CB29-43C6-B829-1F4A475347C5' AND (1 = 1) GROUP BY AssetProp.Id,AssetProp.Name

查询的结果如下，结果中的数据出错： 

idAssetProp	AssetPropName	OrigValue	TotalDepr	NetAmount	MonthDeprAmount
60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	18000	1794.54	16205.46	291.02

使用Distinct关键字去重

 第二次查询的SQL如下：

SELECT AssetProp.Id AS idAssetProp
      ,AssetProp.Name AS AssetPropName
      ,sum(DISTINCT AssetCard.origValue) AS OrigValue
      ,sum(DISTINCT AssetCard.totalDepr) AS TotalDepr
      ,sum(DISTINCT AssetCard.netamount) AS NetAmount
      ,sum(Asset.monthDeprAmount) AS MonthDeprAmount
FROM AM_Asset AS AssetCard
    LEFT JOIN AA_AssetProp AS AssetProp ON AssetCard.idAssetProp = AssetProp.id
    INNER JOIN AM_Asset AS Asset ON Asset.cardid = AssetCard.cardid AND AssetCard.isActive = 1
WHERE 1 = 1 AND (Asset.srcvoucherdate >= '2012-05-01' AND Asset.srcvoucherdate < dateadd(DAY,1,'2012-06-30')) AND Asset.idprocesstype = 'AF3B75A8-CB29-43C6-B829-1F4A475347C5' AND (1 = 1) GROUP BY AssetProp.Id,AssetProp.Name

 第二次查询结果：

idAssetProp	AssetPropName	OrigValue	TotalDepr	NetAmount	MonthDeprAmount
60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	3000	873.01	5126.99	291.02

使用UNION去重

第三次使用Union去重,SQL如下:

SELECT AssetProp.Id AS idAssetProp
      ,AssetProp.Name AS AssetPropName
      ,sum(0) AS OrigValue
      ,sum(0) AS TotalDepr
      ,sum(0) AS NetAmount
      ,sum(Asset.monthDeprAmount) AS MonthDeprAmount
FROM AM_Asset AS AssetCard
    LEFT JOIN AA_AssetProp AS AssetProp ON AssetCard.idAssetProp = AssetProp.id
    INNER JOIN AM_Asset AS Asset ON Asset.cardid = AssetCard.cardid AND AssetCard.isActive = 1
WHERE 1 = 1 AND (Asset.srcvoucherdate >= '2012-05-01' AND Asset.srcvoucherdate < dateadd(DAY,1,'2012-06-30')) AND Asset.idprocesstype = 'AF3B75A8-CB29-43C6-B829-1F4A475347C5' AND (1 = 1) GROUP BY AssetProp.Id,AssetProp.Name
UNION ALL
SELECT AssetProp.Id AS idAssetProp
      ,AssetProp.Name AS AssetPropName
      ,sum(AssetCard.origValue) AS OrigValue
      ,sum(AssetCard.totalDepr) AS TotalDepr
      ,sum(AssetCard.netamount) AS NetAmount
      ,sum(0) AS MonthDeprAmount
FROM AM_Asset AS AssetCard
    LEFT JOIN AA_AssetProp AS AssetProp ON AssetCard.idAssetProp = AssetProp.id
WHERE 1 = 1 AND AssetCard.isActive = 1 AND EXISTS (SELECT id
                                                   FROM AM_Asset AS Asset
                                                   WHERE (Asset.srcvoucherdate >= '2012-05-01' AND Asset.srcvoucherdate < dateadd(DAY,1,'2012-06-30')) AND Asset.idprocesstype = 'AF3B75A8-CB29-43C6-B829-1F4A475347C5' AND (1 = 1) AND Asset.cardid = AssetCard.cardid) GROUP BY AssetProp.Id,AssetProp.Name

 

查询结果:

idAssetProp	AssetPropName	OrigValue	TotalDepr	NetAmount	MonthDeprAmount
60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	0	0	0	291.02
60F10FBD-BD4E-44DA-9736-12C00DEC44E3	固定资产	9000	897.27	8102.73	0

然后对上述结果求和便可达到要求