Date Name Score
1905/6/16 0:00:00 拜仁 胜
1905/6/15 0:00:00 奇才 胜
1905/6/15 0:00:00 湖人 胜
1905/6/14 0:00:00 拜仁 负
1905/6/16 0:00:00 拜仁 负
1905/6/12 0:00:00 奇才 胜要求按以下格式输出
Name 胜 负
拜仁 1 2
湖人 1 0
奇才 2 0
1905/6/16 0:00:00 拜仁 胜
1905/6/15 0:00:00 奇才 胜
1905/6/15 0:00:00 湖人 胜
1905/6/14 0:00:00 拜仁 负
1905/6/16 0:00:00 拜仁 负
1905/6/12 0:00:00 奇才 胜要求按以下格式输出
Name 胜 负
拜仁 1 2
湖人 1 0
奇才 2 0
Name,
sum(case score when '胜 then 1 else 0 end) as '胜',
sum(case score when '负 then 1 else 0 end) as '负'
from
tb
group by
name
Name,
sum(case score when '胜 then 1 else 0 end) as '胜',
sum(case score when '负' then 1 else 0 end) as '负'
from
tb
group by
name
Name,
sum(case score when '胜' then 1 else 0 end) as '胜',
sum(case score when '负' then 1 else 0 end) as '负'
from
tb
group by
name
前两个都掉了'
declare @t table([Date] Datetime,[Name] nvarchar(2),[Score] nvarchar(1))
Insert @t
select '1905/6/16',N'拜仁',N'胜' union all
select '1905/6/15',N'奇才',N'胜' union all
select '1905/6/15',N'湖人',N'胜' union all
select '1905/6/14',N'拜仁',N'负' union all
select '1905/6/16',N'拜仁',N'负' union all
select '1905/6/12',N'奇才',N'胜'
Select [Name],
count(case when [Score]=N'胜' then [Score] end) as 胜,
count(case when [Score]=N'负' then [Score] end) as 负
from @t group by [Name]
/*
Name 胜 负
拜仁 1 2
湖人 1 0
奇才 2 0
*/
declare @t table([Date] Datetime,[Name] nvarchar(2),[Score] nvarchar(1))
Insert @t
select '1905/6/16',N'拜仁',N'胜' union all
select '1905/6/15',N'奇才',N'胜' union all
select '1905/6/15',N'湖人',N'胜' union all
select '1905/6/14',N'拜仁',N'负' union all
select '1905/6/16',N'拜仁',N'负' union all
select '1905/6/12',N'奇才',N'胜'
Select [Name],
sum(charindex(N'胜',[Score])) as 胜,
sum(charindex(N'负',[Score])) as 负
from @t group by [Name]
/*
Name 胜 负
拜仁 1 2
湖人 1 0
奇才 2 0
*/
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
date varchar(20),
name varchar(10),
score varchar(10)
)
go
insert into tb
select '1905/6/16 0:00:00','拜仁','胜' union all
select '1905/6/15 0:00:00','奇才','胜' union all
select '1905/6/15 0:00:00','湖人','胜' union all
select '1905/6/14 0:00:00','拜仁','负' union all
select '1905/6/16 0:00:00','拜仁','负' union all
select '1905/6/12 0:00:00','奇才','胜'
go
--mssql 2000方法
select name,
胜=sum(case when Score='胜' then 1 else 0 end),
负=sum(case when score='负' then 1 else 0 end)
from tb group by name
go
--mssql 2005方法
select * from (select name,score from tb)a pivot(count(score) for score in ([胜],[负])) pvt