34,575
社区成员
发帖
与我相关
我的任务
分享
create table zgke (userid varchar(10),[type] int)
insert into zgke
select 'AA',1
UNION ALL
select 'AA',1
UNION ALL
select 'AA',2
UNION ALL
select 'BB',1
UNION ALL
select 'BB',2
UNION ALL
select 'CC',1
select userid,sum(case type when 1 then 1 else 0 end ) as 'TYPE=1',
sum(case type when 2 then 1 else 0 end ) as 'TYPE=2'
from zgke group by userid
=================
USERID TYPE=1 TYPE=2
---------- ----------- -----------
AA 2 1
BB 1 1
CC 1 0
create table #t (userid varchar(10),[type] int)
insert into #t
select 'AA',1
UNION ALL
select 'AA',1
UNION ALL
select 'AA',2
UNION ALL
select 'BB',1
UNION ALL
select 'BB',2
UNION ALL
select 'CC',1
SELECT USERID,[TYPE=1]=(SELECT COUNT(1) FROM #T WHERE USERID=A.USERID AND [TYPE]=1),[TYPE=2]=(SELECT COUNT(1) FROM #T WHERE USERID=A.USERID AND [TYPE]=2) FROM #T A
GROUP BY USERID
USERID TYPE=1 TYPE=2
---------- ----------- -----------
AA 2 1
BB 1 1
CC 1 0
(3 行受影响)