sql server 经典SQL——分组统计

一、分组统计

数据

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 

还可以用子查询