SQL查询修改/优化
问题描述:
我想弄清楚如何将这3个查询合并为一个百分比列,我还没有弄清楚。任何人都可以协助SQL查询修改/优化
Select DISTINCT a.ASSN As Association, SUM(tonnage_adjusted) as TotalTonnage
From DeliveryTons d INNER JOIN ReapingGroups a ON d.reaping_code = a.REAPING_GROUP_CODE
WHERE reaping_code IS NOT NULL
Group By a.ASSN
ORDER BY Association
Select DISTINCT a.ASSN As Association, SUM(tonnage_adjusted) as Monitored
From DeliveryTons d INNER JOIN ReapingGroups a ON d.reaping_code = a.REAPING_GROUP_CODE
WHERE remarks = '' AND reaping_code IS NOT NULL
Group By a.ASSN
ORDER BY Association
Select DISTINCT a.ASSN As Association, SUM(tonnage_adjusted) as NotMonitored
From DeliveryTons d INNER JOIN ReapingGroups a ON d.reaping_code = a.REAPING_GROUP_CODE
WHERE remarks = 'NO_TICKET' AND reaping_code IS NOT NULL
Group By a.ASSN
ORDER BY Association
答
这应该起作用。
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 ReapingGroups a
ON d.reaping_code = a.REAPING_GROUP_CODE
WHERE d.reaping_code IS NOT NULL
Group By a.ASSN
)
SELECT Association
,TotalTonnage
,Monitored
,NotMonitored
,((Monitored/TotalTonnage) * 100) as pct_Monitored
FROM Summary
Order by Association
百分比列的公式是什么?另外,你使用的是什么版本的SQL服务器? – Sparrow
它应该是什么监视除以totaltonnage时间100. im使用Microsoft SQL Server 2008 – ivias