34,587
社区成员
发帖
与我相关
我的任务
分享
create table Result
(
[Date] Date not null,
[Result] varchar(2)
);
insert into Result values('2011-01-01', '胜');
insert into Result values('2011-01-02', '胜');
insert into Result values('2011-01-03', '胜');
insert into Result values('2011-01-03', '负');
insert into Result values('2011-01-03', '负');
insert into Result values('2011-01-04', '胜');
insert into Result values('2011-01-04', '胜');
insert into Result values('2011-01-04', '胜');
select Date,
Win = SUM(case Result when '胜' then 1 else 0 end),
Lose = Sum(case Result when '负' then 1 else 0 end)
from Result
Group by Date;
select
CONVERT(VARCHAR(10) , DATE, 120),
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)
select CONVERT(varchar(10),[Date],120) as [Date],
胜=SUM(case when [Result]='胜' then 1 else 0 end),
负=SUM(case when [Result]='负' then 1 else 0 end)
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
*/
静态实现
--> 测试数据:[tbl]
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','胜'
declare @str varchar(1000)
set @str=''
select @str=@str+','+[Result]+'=sum(case when [Result]='
+QUOTENAME([Result],'''')+' then 1 else 0 end)' from tbl
group by [Result]
exec('select convert(varchar(10),[Date],120) as [Date] '+@str+
' 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
*/