select [day],胜=sum(case when result='胜' then 1 else 0 end) ,负=sum(case when result='负' then 1 else 0 end) from tb group by [day]
create table #tb([day] varchar(10),result varchar(10)) insert into #tb select '2013-05-09','胜' union all select '2013-05-09','胜' union all select '2013-05-09','负' union all select '2013-05-09','负' union all select '2013-05-10','胜' union all select '2013-05-10','负' union all select '2013-05-10','负' select [day] as 日期,胜=sum(case when result='胜' then 1 else 0 end) ,负=sum(case when result='负' then 1 else 0 end) from #tb group by [day]/* 日期 胜 负 --------------------------- 2013-05-09 2 2 2013-05-10 1 2 */
SELECT [DAY] AS '日期',COUNT(CASE WHEN result ='胜' THEN 1 ELSE 0 END ) AS '胜' ,COUNT(CASE WHEN result ='负' THEN 1 ELSE 0 END ) AS '负' FROM temp GROUP BY [DAY]
declare @s varchar(max)select @s=ISNULL(@s+',','')+'['+result+']'from #tb group by [Result] set @s='select * from #tb pivot (count(Result) for result in ('+@s+')) a'exec(@s)
select t.[Day],[胜],[负] from ( select [Day],Result,COUNT(*) as total from Temp group by [Day],Result ) t pivot ( sum(total) for Result in ('胜','负') ) as pvt
select rq,sum(case result when '胜' then 1 end) 胜,sum(case result when '负' then 1 end) 负 from tkgroup by rq
create table #tb([day] varchar(10),result varchar(10)) insert into #tb select '2013-05-09','胜' union all select '2013-05-09','胜' union all select '2013-05-09','负' union all select '2013-05-09','负' union all select '2013-05-10','胜' union all select '2013-05-10','负' union all select '2013-05-10','负';with cte as ( select day,result,COUNT(*) as coun from #tb group by day,result ) select day, max(case result when '胜' then coun end) '胜', max(case result when '负' then coun end) '负' from cte group by day --------------------------------------------------------------- day 胜 负 ---------- ----------- ----------- 2013-05-09 2 2 2013-05-10 1 2(2 行受影响)
select [Day],sum(case result when '胜' then 1 end) 胜,sum(case result when '负' then 1 end) 负 from Temp group by [Day]
SELECT [DAY] AS '日期',COUNT(CASE WHEN result ='胜' THEN 1 ELSE 0 END ) AS '胜' ,COUNT(CASE WHEN result ='负' THEN 1 ELSE 0 END ) AS '负' FROM temp GROUP BY [DAY]
,负=sum(case when result='负' then 1 else 0 end)
from tb
group by [day]
insert into #tb
select '2013-05-09','胜'
union all select '2013-05-09','胜'
union all select '2013-05-09','负'
union all select '2013-05-09','负'
union all select '2013-05-10','胜'
union all select '2013-05-10','负'
union all select '2013-05-10','负'
select [day] as 日期,胜=sum(case when result='胜' then 1 else 0 end)
,负=sum(case when result='负' then 1 else 0 end)
from #tb
group by [day]/*
日期 胜 负
---------------------------
2013-05-09 2 2
2013-05-10 1 2
*/
,COUNT(CASE WHEN result ='负' THEN 1 ELSE 0 END ) AS '负'
FROM temp
GROUP BY [DAY]
set @s='select * from #tb pivot (count(Result) for result in ('+@s+')) a'exec(@s)
select t.[Day],[胜],[负] from
(
select [Day],Result,COUNT(*) as total from Temp
group by [Day],Result
) t
pivot
(
sum(total) for Result in ('胜','负')
) as pvt
select rq,sum(case result when '胜' then 1 end) 胜,sum(case result when '负' then 1 end) 负 from tkgroup by rq
create table #tb([day] varchar(10),result varchar(10))
insert into #tb
select '2013-05-09','胜'
union all select '2013-05-09','胜'
union all select '2013-05-09','负'
union all select '2013-05-09','负'
union all select '2013-05-10','胜'
union all select '2013-05-10','负'
union all select '2013-05-10','负';with cte as
(
select
day,result,COUNT(*) as coun
from #tb
group by day,result
)
select day,
max(case result when '胜' then coun end) '胜',
max(case result when '负' then coun end) '负'
from cte
group by day
---------------------------------------------------------------
day 胜 负
---------- ----------- -----------
2013-05-09 2 2
2013-05-10 1 2(2 行受影响)
,COUNT(CASE WHEN result ='负' THEN 1 ELSE 0 END ) AS '负'
FROM temp
GROUP BY [DAY]