27,579
社区成员
发帖
与我相关
我的任务
分享
select convert(varchar(10),matchTime,120) as 比赛时间,
sum(v) as 胜,
sum(f) as 负
from(
select matchTime,1 as v,0 as f from Match where result=1
union all
select matchTime,0,1 from Match where result=0
) t
group by convert(varchar(10),matchTime,120)
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-06 22:40:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([matchTime] datetime,[result] varchar(5))
insert [tb]
select '2009-11-5','1,0,1' union all
select '2009-11-6','1,0' union all
select '2009-11-7','1,0'
--------------开始查询--------------------------
;with f as
(
select
a.matchTime,b.result
from
(select matchTime,result=convert(xml,'<root><v>'+replace(result,',','</v><v>')+'</v></root>') from Tb)a
outer apply
(select result=C.v.value('.','nvarchar(100)') from a.result.nodes('/root/v')C(v))b
)
select
convert(varchar(10),matchtime,120) as matchTime,
sum(case result when 1 then 1 else 0 end) as 胜,
sum(case result when 0 then 1 else 0 end) as 输
from
f
group by
convert(varchar(10),matchtime,120)
----------------结果----------------------------
/* matchTime 胜 输
---------- ----------- -----------
2009-11-05 2 1
2009-11-06 1 1
2009-11-07 1 1
(3 行受影响)
*/
行列转换
select
convert(varchar(10),matchtime,120) as matchTime,
max(case result when 1 then 1 else 0 end) as 胜,
max(case result when 0 then 1 else 0 end) as 输
from
tb
group by
convert(varchar(10),matchtime,120)