一、分组统计
数据
name dtdate result
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
结果
name dtdate win lose
aa 2017-01-04 2 0
bb 2017-01-04 2 0
cc 2017-01-04 0 1
dd 2017-01-04 1 0
aa 2017-01-05 1 1
演示代码
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
还可以用子查询