SQL查询需要修订

debugcn 发表于 Dev

伊维亚斯

我有以下查询：

With Summary as (
Select a.ASSN As Association
      ,SUM(tonnage_adjusted) as TotalTonnage
      ,SUM(case when remarks = '' THEN tonnage_adjusted ELSE NULL END) as Monitored
      ,SUM(case when remarks = 'NO_TICKET' THEN tonnage_adjusted ELSE NULL END) as NotMonitored
From DeliveryTons d INNER JOIN FarmerGroups a ON d.reaping_code = a.REAPING_GROUP_CODE 
WHERE reaping_code IS NOT NULL AND d.crop_season = 4 
Group By a.ASSN
)

SELECT Association
  ,TotalTonnage
  , COALESCE(Monitored,0) As Monitored
  , COALESCE(NotMonitored,0)As NotMonitored
  , COALESCE(((Monitored/TotalTonnage) * 100),0) as pct_Monitored
FROM Summary 
Order by Association,TotalTonnage

以及下表示例：

交付吨

id | parcel_id | crop_season | tonnage_adjusted | reaping_code | remarks 
1    012-0075       4               25.60            NSCGA12     
2    011-0089       3               17.58            PSCPA      NO_TICKET 
3    001-0541       3               14.58            PSCPA       
4                   2               18.50            NSCGA12    NO_TICKER

农民团体

ID | ASSN  |   REAPING_GROUP_CODE  |  CROP_SEASON
1    CSCPA        NSCGA12                  4
2    PSCPA        PSCPA                    3
3    PSCPA        NSCGA12                  4

我想按关联关系获取tonage_adjusted的总数，但是我似乎得到的ttonage_adjusted的总和比crop_season实际得到的高。几乎与从其他crop_seasons获取数据一样。谁能看到sql有什么问题吗？

凯井

似乎该联接从FarmGroups表中提取了多个匹配行，从而产生了叉积。

我猜应该不会，但是现在您有多个相同的ReapingCode / Season组合条目。

也许您想清理数据并添加一个，UNIQUE KEY(REAPING_GROUP_CODE, CROP_SEASON)以避免将来出现此类重复。如果我理解正确，则特定SEASON中的单个REAPING_GROUP_CODE只能计入一个ASSN。

如果那不可能，您仍然可以这样做以消除查询时的重复项，但是它的效果不如清理+模式更改：

With Summary as (
Select a.ASSN As Association
      ,SUM(tonnage_adjusted) as TotalTonnage
      ,SUM(case when remarks = '' THEN tonnage_adjusted ELSE NULL END) as Monitored
      ,SUM(case when remarks = 'NO_TICKET' THEN tonnage_adjusted ELSE NULL END) as NotMonitored
FROM DeliveryTons d 
    /* NOTE the SELECT DISTINCT subquery here */
    INNER JOIN 
    (SELECT DISTINCT 
         ASSN,REAPING_CODE_GROUP,CROP_SEASON FROM FarmerGroups 
         WHERE CROP_SEASON = 4
    ) a 
    ON d.reaping_code = a.REAPING_GROUP_CODE 
    AND d.crop_season = a.CROP_SEASON
WHERE reaping_code IS NOT NULL 
Group By a.ASSN
)

SELECT Association
  ,TotalTonnage
  , COALESCE(Monitored,0) As Monitored
  , COALESCE(NotMonitored,0)As NotMonitored
  , COALESCE(((Monitored/TotalTonnage) * 100),0) as pct_Monitored
FROM Summary 
Order by Association,TotalTonnage

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

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