按日期求和.txt

来自「SQL语言常用的一些命令各代码」· 文本 代码 · 共 23 行

declare @tab table(rq varchar(10),胜负 char(2))
insert into @tab 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 a.rq,a.胜 ,b.负 from 
(select distinct(rq) , count(胜负)as 胜
from @tab
where 胜负='胜'
group by rq)a
inner join
(select distinct(rq) , count(胜负)as 负
from @tab
where 胜负='负'
group by rq) b
on a.rq=b.rq
 /*解法二*/  
select rq,sum(case 胜负 when '胜' then 1 else 0 end)as 胜 ,sum(case 胜负 when  '负'  then 1 else 0 end)as 负
from @tab group by rq