十万条数据，求按条件SQL分类汇总。
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
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 行受影响) */
