- SQL code
create table #temp( id int identity(1,1), --自动编号 tName nvarchar(100), --姓名 tCity nvarchar(100), --城市 tMonth int, --月 tPrice decimal(12,2), --金额 tFlag int --是否收到,1收到，0未收到)Insert #temp select 'a','北京',1,100.00,1 union allselect 'b','北京',1,100.00,1 union allselect 'c','北京',1,100.00,1 union allselect 'd','重庆',1,100.00,1 union allselect 'e','重庆',1,100.00,1 union allselect 'f','重庆',1,100.00,1 union allselect 'g','上海',1,100.00,1 union allselect 'h','上海',1,100.00,1 union allselect 'i','上海',1,100.00,1 union all select 'a','北京',2,100.00,1 union allselect 'b','北京',2,100.00,1 union allselect 'c','北京',2,100.00,0 union allselect 'd','重庆',2,100.00,1 union allselect 'e','重庆',2,100.00,1 union allselect 'f','重庆',2,100.00,0 union allselect 'g','上海',2,100.00,0 union allselect 'h','上海',2,100.00,1 union allselect 'i','上海',2,100.00,0 union allselect 'a','北京',3,100.00,0 union allselect 'b','北京',3,100.00,1 union allselect 'c','北京',3,100.00,0 union allselect 'd','重庆',3,100.00,0 union allselect 'e','重庆',3,100.00,1 union allselect 'f','重庆',3,100.00,0 union allselect 'g','上海',3,100.00,1 union allselect 'h','上海',3,100.00,1 union allselect 'i','上海',3,100.00,0 union allselect 'a','北京',4,100.00,1 union allselect 'b','北京',4,100.00,0 union allselect 'c','北京',4,100.00,0 union allselect 'd','重庆',4,100.00,1 union allselect 'e','重庆',4,100.00,0 union allselect 'f','重庆',4,100.00,0 union allselect 'g','上海',4,100.00,1 union allselect 'h','上海',4,100.00,0 union allselect 'i','上海',4,100.00,0 union all select 'a','北京',5,100.00,0 union allselect 'b','北京',5,100.00,0 union allselect 'c','北京',5,100.00,0 union allselect 'd','重庆',5,100.00,0 union allselect 'e','重庆',5,100.00,0 union allselect 'f','重庆',5,100.00,0 union allselect 'g','上海',5,100.00,0 union allselect 'h','上海',5,100.00,0 union allselect 'i','上海',5,100.00,0
汇总格式如下，按城市分组,每行中的one_All 指的是该城市所有tMonth等于1的 tPrice 和，
one_No 指的是该城市所有tMonth等于1且tFlag等于0的 tPrice 和，
two_All 指的是该城市所有tMonth等于2的 tPrice 和，
two_No 指的是该城市所有tMonth等于2且tFlag等于0的 tPrice 和，
以此类推。
每个月数据量都很大的情况下，如何汇总？
--tCity | one_All | one_No | two_All | two_No | three_All | three_No | Four_All | Four_No | Five_All | Five_No
----------------------------------------
------解决方案--------------------
- SQL code
SELECT tCity,SUM(one_All)one_All,SUM(one_No)one_No,SUM(two_All)two_All,SUM(two_No)two_No,SUM(three_All)three_All,SUM(three_No)three_No, SUM(Four_All)Four_All,SUM(Four_No)Four_No,SUM(Five_All)Five_All,SUM(Five_No)Five_No FROM ( SELECT tCity,CASE WHEN tMonth=1 THEN SUM(tPrice ) END one_All,CASE WHEN tMonth=1 AND tFlag=0 THEN SUM(tPrice ) END one_No, CASE WHEN tMonth=2 THEN SUM(tPrice ) END two_All,CASE WHEN tMonth=2 AND tFlag=0 THEN SUM(tPrice ) END two_No ,CASE WHEN tMonth=3 THEN SUM(tPrice ) END three_All,CASE WHEN tMonth=3 AND tFlag=0 THEN SUM(tPrice ) END three_No, CASE WHEN tMonth=4 THEN SUM(tPrice ) END Four_All,CASE WHEN tMonth=4 AND tFlag=0 THEN SUM(tPrice ) END Four_No ,CASE WHEN tMonth=5 THEN SUM(tPrice ) END Five_All,CASE WHEN tMonth=5 AND tFlag=0 THEN SUM(tPrice ) END Five_No FROM #temp GROUP BY tCity,tMonth,tFlag )a GROUP BY a.tCity /* tCity one_All one_No two_All two_No three_All three_No Four_All Four_No Five_All Five_No ---------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- 北京 300.00 NULL 300.00 100.00 300.00 200.00 300.00 200.00 300.00 300.00 上海 300.00 NULL 300.00 200.00 300.00 100.00 300.00 200.00 300.00 300.00 重庆 300.00 NULL 300.00 100.00 300.00 200.00 300.00 200.00 300.00 300.00 警告: 聚合或其他 SET 操作消除了 Null 值。 (3 行受影响) */