表HorseRace(Time datetime,result char(2))
有如下数据
‘2005-05-09’ 胜
‘2005-05-09’ 胜
‘2005-05-09’ 负
‘2005-05-09’ 负
‘2005-05-10’ 胜
‘2005-05-10’ 负
‘2005-05-10’ 负
要求生成如下结果胜 负
2005-05-09 2 2
2005-05-10 1 2的SQL语句怎么写
有如下数据
‘2005-05-09’ 胜
‘2005-05-09’ 胜
‘2005-05-09’ 负
‘2005-05-09’ 负
‘2005-05-10’ 胜
‘2005-05-10’ 负
‘2005-05-10’ 负
要求生成如下结果胜 负
2005-05-09 2 2
2005-05-10 1 2的SQL语句怎么写
SUM(CASE WHEN result='胜' THEN 1 ELSE 0 END)AS 胜,
SUM(CASE WHEN result='败' THEN 1 ELSE 0 END)AS 败
FROM TB GROUP BY CONVERT(VARCHAR(10),TIME,120)
convert(varchar(10),Time,120) as [time],
sum(case result when '胜' then 1 else 0 end) as '胜',
sum(case result when '负' then 1 else 0 end) as '负'
from
tb
group by
convert(varchar(10),Time,120)
insert #tb
select '2005-05-09','胜' union all
select '2005-05-09','胜' union all
select '2005-05-09','负' union all
select '2005-05-09','负' union all
select '2005-05-10','胜' union all
select '2005-05-10','负' union all
select '2005-05-10','负'
正式语句:
select time,sum(case when result='胜'then 1 else 0 end ) as 胜,
sum(case when result='负'then 1 else 0 end ) as 负
from #tb
group by time
日期,
SUM(CASE WHEN 结果='胜' THEN 1 ELSE 0 END)AS 胜,
SUM(CASE WHEN 结果='败' THEN 1 ELSE 0 END)AS 败
FROM TB GROUP BY 日期
SUM(CASE WHEN result='胜' THEN 1 ELSE 0 END)AS 胜,
SUM(CASE WHEN result='败' THEN 1 ELSE 0 END)AS 败
FROM TB GROUP BY CONVERT(VARCHAR(10),TIME,120)