create table tb(a datetime, b char(2))
insert into tb values('2009-3-10','胜')
insert into tb values('2009-3-10','败')
insert into tb values('2009-3-10','胜')
insert into tb values('2009-3-15','胜')
insert into tb values('2009-3-15','败')
insert into tb values('2009-3-15','败')select convert(varchar(11),a,120) as a ,sum(case b when '胜' then 1 else 0 end ) '胜利', sum(case b when '败' then 1 else 0 end ) '失败'
from tb
group by convert(varchar(11),a,120)
insert into tb values('2009-3-10','胜')
insert into tb values('2009-3-10','败')
insert into tb values('2009-3-10','胜')
insert into tb values('2009-3-15','胜')
insert into tb values('2009-3-15','败')
insert into tb values('2009-3-15','败')select convert(varchar(11),a,120) as a ,sum(case b when '胜' then 1 else 0 end ) '胜利', sum(case b when '败' then 1 else 0 end ) '失败'
from tb
group by convert(varchar(11),a,120)
(时间 datetime,成绩 nvarchar(4))insert into ball
select '2009-3-10', '胜'
union all
select '2009-3-10', '败'
union all
select '2009-3-10', '胜'
union all
select '2009-3-15', '胜'
union all
select '2009-3-15', '败'
union all
select '2009-3-15', '败'
select convert(nvarchar(10),时间,120)as 时间,
胜利 = sum(case when 成绩 = '胜' then 1 else 0 end),
失败 = sum(case when 成绩 = '败' then 1 else 0 end)
from ball
group by convert(nvarchar(10),时间,120)时间 胜利 失败
---------- ----------- -----------
2009-03-10 2 1
2009-03-15 1 2(2 row(s) affected)
from tb
group by 时间
insert @t select '2009-3-10','胜'
insert @t select '2009-3-10','败'
insert @t select '2009-3-10','胜'
insert @t select '2009-3-15','胜'
insert @t select '2009-3-15','败'
insert @t select '2009-3-15','败'
select 时间,胜利=sum(case 成绩 when '胜' then 1 else 0 end),失败=sum(case 成绩 when '败' then 1 else 0 end) from @t group by 时间时间 胜利 失败
---------- ----------- -----------
2009-3-10 2 1
2009-3-15 1 2(2 行受影响)
from tb
group by a
select convert(varchar(11),a,120) as 时间,胜 as 胜利,败 as 失败
from(
select * from tb
pivot
(count(b) for b in ([胜],[败])) as p)
as tb