一个SQL语句的变化过程

1  原始语句

SELECT C.ParentId,A.Area_DM, A.Airport_DM, ('        ' + B.QC) AS AirportName,Sum(JCN) AS TotalJCN, 
SUM(CR + ET + CRWH + ETWH)  AS TotalLK, Sum(HW + YJ)  AS TotalHY, Sort = 1 
FROM AirportProduces_2006 A 
LEFT JOIN Airports B ON A.Airport_DM = B.Airport_DM 
LEFT JOIN Areas AS C ON C.Area_DM = A.Area_DM
LEFT JOIN HBXZ AS D ON A.HBXZ = D.CHAR_CODE
WHERE A.Produce_Date >= '2006-01-01' AND A.Produce_Date <= '2006-02-28'
AND D.CHAR_SORT = '运输'
AND  (A.Area_DM = '51')  GROUP BY A.Area_DM, A.Airport_DM, B.QC, C.ParentId,D.CHAR_SORT

2 变化要求：当Sum(JCN)的时候不考虑where条件中的D.CHAR_SORT

   变化第一步：

SELECT C.ParentId,A.Area_DM, A.Airport_DM, ('        ' + B.QC) AS AirportName,Sum(JCN) AS TotalJCN, 
CASE WHEN D.CHAR_SORT = '运输' THEN SUM(CR + ET + CRWH + ETWH) ELSE 0 END AS TotalLK, 
CASE WHEN D.CHAR_SORT = '运输' THEN Sum(HW + YJ) ELSE 0 END AS TotalHY, Sort = 1 INTO #TEMP
FROM AirportProduces_2006 A 
LEFT JOIN Airports B ON A.Airport_DM = B.Airport_DM 
LEFT JOIN Areas AS C ON C.Area_DM = A.Area_DM
LEFT JOIN HBXZ AS D ON A.HBXZ = D.CHAR_CODE
WHERE A.Produce_Date >= '2006-01-01' AND A.Produce_Date <= '2006-02-28'
AND (A.Area_DM = '51') GROUP BY A.Area_DM, A.Airport_DM, B.QC, C.ParentId,D.CHAR_SORT

SELECT ParentId,Area_DM,Airport_DM,AirportName, 
SUM(TotalJCN) AS TotalJCN,SUM(TotalLK) AS TotalLK,SUM(TotalHY) AS TotalHY,Sort = 1 FROM #TEMP2 
GROUP BY Area_DM, Airport_DM, ParentId, AirportName

这个步骤运用临时表  将结果放入临时表 然后再分组统计

2 演化步骤二

综合第一步，可以写出第二步，一条语句

SELECT ParentId,Area_DM,Airport_DM,AirportName, 
SUM(TotalJCN) AS TotalJCN,SUM(TotalLK) AS TotalLK,SUM(TotalHY) AS TotalHY,Sort = 1 FROM (
SELECT C.ParentId,A.Area_DM, A.Airport_DM, ('        ' + B.QC) AS AirportName,
Sum(JCN) AS TotalJCN, 
CASE WHEN D.CHAR_SORT = '运输' THEN SUM(CR + ET + CRWH + ETWH) ELSE 0 END AS TotalLK, 
CASE WHEN D.CHAR_SORT = '运输' THEN Sum(HW + YJ) ELSE 0 END AS TotalHY, Sort = 1 FROM
AirportProduces_2006 A 
LEFT JOIN Airports B ON A.Airport_DM = B.Airport_DM 
LEFT JOIN Areas AS C ON C.Area_DM = A.Area_DM
LEFT JOIN HBXZ AS D ON A.HBXZ = D.CHAR_CODE
WHERE A.Produce_Date >= '2006-01-01' AND A.Produce_Date <= '2006-02-28'
AND (A.Area_DM = '51') GROUP BY A.Area_DM, A.Airport_DM, B.QC, C.ParentId,D.CHAR_SORT
) AS SUMTable GROUP BY Area_DM, Airport_DM, ParentId, AirportName