数据分组汇总后运算
本帖最后由 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 行受影响)

*/