go
if object_id('[tbl]') is not null
drop table [tbl]
go
create table [tbl](
[Date] datetime,
[Result] varchar(2)
)
go
insert [tbl]
select '2011-01-01','胜' union all
select '2011-01-02','胜' union all
select '2011-01-03','胜' union all
select '2011-01-03','负' union all
select '2011-01-03','负' union all
select '2011-01-04','胜' union all
select '2011-01-04','胜' union all
select '2011-01-04','胜'
select * from [tbl]
/*
*
Date Result
2011-01-01 00:00:00.000 胜
2011-01-02 00:00:00.000 胜
2011-01-03 00:00:00.000 胜
2011-01-03 00:00:00.000 负
2011-01-03 00:00:00.000 负
2011-01-04 00:00:00.000 胜
2011-01-04 00:00:00.000 胜
2011-01-04 00:00:00.000 胜
*/
select
CONVERT(VARCHAR(10) , DATE, 120) AS 'DATE',
sum(case when result='胜' then 1 else 0 end) as '胜',
sum(case when result='负' then 1 else 0 end) as '负'
from tbl group by CONVERT(VARCHAR(10) , DATE, 120)
/*
DATE 胜 负
2011-01-01 1 0
2011-01-02 1 0
2011-01-03 1 2
2011-01-04 3 0
*/
SQLSERVER 分组后统计
最新推荐文章于 2019-08-23 09:53:49 发布