sql server 经典SQL——分组统计

liwya

　　一、分组统计
　　数据



namedtdateresult
aa2017-01-041
aa2017-01-041
aa2017-01-051
aa2017-01-050
bb2017-01-041
bb2017-01-041
cc2017-01-040
dd2017-01-041

　　结果



namedtdatewinlose
aa2017-01-0420
bb2017-01-0420
cc2017-01-0401
dd2017-01-0410
aa2017-01-0511

　　演示代码



create table #t
(
name varchar(48),
dtdate date,
result int
)
insert into #t
values
('aa','2017-01-04',1),
('aa','2017-01-04',1),
('aa','2017-01-05',1),
('aa','2017-01-05',0),
('bb','2017-01-04',1),
('bb','2017-01-04',1),
('cc','2017-01-04',0),
('dd','2017-01-04',1)
select name,dtdate,SUM(case when result=1 then 1 else 0 end) 'win',
sum(case when result=0 then 1 else 0 end ) 'lose'
from #t group by name,dtdate

　　还可以用子查询