34,576
社区成员
发帖
与我相关
我的任务
分享
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 all
select 'b','北京',1,100.00,1 union all
select 'c','北京',1,100.00,1 union all
select 'd','重庆',1,100.00,1 union all
select 'e','重庆',1,100.00,1 union all
select 'f','重庆',1,100.00,1 union all
select 'g','上海',1,100.00,1 union all
select 'h','上海',1,100.00,1 union all
select 'i','上海',1,100.00,1 union all
select 'a','北京',2,100.00,1 union all
select 'b','北京',2,100.00,1 union all
select 'c','北京',2,100.00,0 union all
select 'd','重庆',2,100.00,1 union all
select 'e','重庆',2,100.00,1 union all
select 'f','重庆',2,100.00,0 union all
select 'g','上海',2,100.00,0 union all
select 'h','上海',2,100.00,1 union all
select 'i','上海',2,100.00,0 union all
select 'a','北京',3,100.00,0 union all
select 'b','北京',3,100.00,1 union all
select 'c','北京',3,100.00,0 union all
select 'd','重庆',3,100.00,0 union all
select 'e','重庆',3,100.00,1 union all
select 'f','重庆',3,100.00,0 union all
select 'g','上海',3,100.00,1 union all
select 'h','上海',3,100.00,1 union all
select 'i','上海',3,100.00,0 union all
select 'a','北京',4,100.00,1 union all
select 'b','北京',4,100.00,0 union all
select 'c','北京',4,100.00,0 union all
select 'd','重庆',4,100.00,1 union all
select 'e','重庆',4,100.00,0 union all
select 'f','重庆',4,100.00,0 union all
select 'g','上海',4,100.00,1 union all
select 'h','上海',4,100.00,0 union all
select 'i','上海',4,100.00,0 union all
select 'a','北京',5,100.00,0 union all
select 'b','北京',5,100.00,0 union all
select 'c','北京',5,100.00,0 union all
select 'd','重庆',5,100.00,0 union all
select 'e','重庆',5,100.00,0 union all
select 'f','重庆',5,100.00,0 union all
select 'g','上海',5,100.00,0 union all
select 'h','上海',5,100.00,0 union all
select '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 行受影响)
*/