数据分组汇总后运算
本帖最后由 lshfong 于 2012-12-09 14:50:25 编辑 表user数据结构如下
id userid sdate
1 aaa 1
2 aaa 1
3 bbb 0
4 bbb 1
5 bbb 1
6 aaa 0
7 aaa 0
8 aaa 0
用userid分组后怎么能同时得到sdate为1和sdate为0的有多少数据
userid sdate总数 sdate为1 sdate为0
aaa 5 2 3
bbb 3 2 1
[最优解释]
CREATE TABLE [user]
(
id INT ,
userid VARCHAR(10) ,
sdate INT
)
INSERT INTO [user]
SELECT 1 ,
'aaa' ,
1
UNION ALL
SELECT 2 ,
'aaa' ,
1
UNION ALL
SELECT 3 ,
'bbb' ,
0
UNION ALL
SELECT 4 ,
'bbb' ,
1
UNION ALL
SELECT 5 ,
'bbb' ,
1
UNION ALL
SELECT 6 ,
'aaa' ,
0
UNION ALL
SELECT 7 ,
'aaa' ,
0
UNION ALL
SELECT 8 ,
'aaa' ,
0
SELECT userid ,
[sdate总数] = COUNT(CASE WHEN sdate = 0 THEN 1
ELSE NULL
END) + COUNT(CASE WHEN sdate = 1 THEN 1
ELSE NULL
END) ,
[sdate为1] = COUNT(CASE WHEN sdate = 1 THEN 1
ELSE NULL
END) ,
[sdate为0] = COUNT(CASE WHEN sdate = 0 THEN 1
ELSE NULL
END)
FROM [user]
GROUP BY userid
/*
userid sdate总数 sdate为1 sdate为0
---------- ----------- ----------- -----------
aaa 5 2 3
bbb 3 2 1
警告: 聚合或其他 SET 操作消除了 Null 值。
(2 行受影响)
*/
SELECT userid ,
[sdate为1] = COUNT(CASE WHEN sdate = 1 THEN 1
ELSE NULL
END) ,
[sdate为0] = COUNT(CASE WHEN sdate = 0 THEN 1
ELSE NULL
END) ,
[sdate总数] = COUNT(CASE WHEN sdate = 0 THEN 1
ELSE NULL
END) + COUNT(CASE WHEN sdate = 1 THEN 1
ELSE NULL
END)
FROM [user]
GROUP BY userid
USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST;
GO
CREATE TABLE TEST
(
id int identity,userid char(3),sdate int
);
GO
INSERT INTO TEST VALUES
('aaa',1),
('aaa',1),
('bbb',0),
('bbb',1),
('bbb',1),
('aaa',0),
('aaa',0),
('aaa',0);
GO
----------------------------------------
select userid,sdate_sum,sdate_1,sdate_0
from
(
select userid,'sdate_sum'as stype,COUNT(1)as scount
from TEST
group by userid
union all
select userid,'sdate_'+cast(sdate as varchar(10)),COUNT(1)as scount
from TEST
group by userid,cast(sdate as varchar(10))
) as a
pivot (max(scount) for stype in (sdate_sum,sdate_1,sdate_0))p
----------------------------------------
/*
userid sdate_sum sdate_1 sdate_0
------ ----------- ----------- -----------
aaa 5 2 3
bbb 3 2 1
(2 行受影响)
*/
