现在有表数据如下:zyxm yf df
田科 2007-02 50.4000
张玉 2007-02 46.4000
周锡镛 2007-02 50.4000
田科 2007-03 48.8000
张玉 2007-03 46.4000
2007-04
....
2007-12我要实现的结果是将姓名显示一个,然后按月份显示得分,如下结果:
zyxm 1月份 2月份 3月份 4月份 .......
田科 50.40 48.80 ...
张玉 46.40 46.40 46.40请大哥们指教!
田科 2007-02 50.4000
张玉 2007-02 46.4000
周锡镛 2007-02 50.4000
田科 2007-03 48.8000
张玉 2007-03 46.4000
2007-04
....
2007-12我要实现的结果是将姓名显示一个,然后按月份显示得分,如下结果:
zyxm 1月份 2月份 3月份 4月份 .......
田科 50.40 48.80 ...
张玉 46.40 46.40 46.40请大哥们指教!
select zyxm,
sum(case when month(yf)=1 then df else 0 end ) as [1月份],
sum(case when month(yf)=2 then df else 0 end ) as [2月份]
--...
from tb
where year(yf)=2007
group by zyxm
-- Author : htl258(Tony)
-- Date : 2010-05-07 10:53:53
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([zyxm] [nvarchar](10),[yf] [nvarchar](10),[df] [numeric](6,4))
INSERT INTO [tb]
SELECT '田科','2007-02','50.4000' UNION ALL
SELECT '张玉','2007-02','46.4000' UNION ALL
SELECT '周锡镛','2007-02','50.4000' UNION ALL
SELECT '田科','2007-03','48.8000' UNION ALL
SELECT '张玉','2007-03','46.4000'--SELECT * FROM [tb]-->SQL查询如下:select [zyxm]
,sum(case m when 1 then [df] else 0 end) [1月份]
,sum(case m when 2 then [df] else 0 end) [2月份]
,sum(case m when 3 then [df] else 0 end) [3月份]
,sum(case m when 4 then [df] else 0 end) [4月份]
,sum(case m when 5 then [df] else 0 end) [5月份]
,sum(case m when 6 then [df] else 0 end) [6月份]
,sum(case m when 7 then [df] else 0 end) [7月份]
,sum(case m when 8 then [df] else 0 end) [8月份]
,sum(case m when 9 then [df] else 0 end) [9月份]
,sum(case m when 10 then [df] else 0 end) [10月份]
,sum(case m when 11 then [df] else 0 end) [11月份]
,sum(case m when 12 then [df] else 0 end) [12月份]
from (
select 1 m union select 2 union select 3 union
select 4 m union select 5 union select 6 union
select 7 m union select 8 union select 9 union
select 10 union select 11 union select 12
) a
left join [tb] b
on a.m=right(yf,2)*1
group by [zyxm]
having zyxm is not null
/*
zyxm 1月份 2月份 3月份 4月份 5月份 6月份 7月份 8月份 9月份 10月份 11月份 12月份
---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
田科 0.0000 50.4000 48.8000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
张玉 0.0000 46.4000 46.4000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
周锡镛 0.0000 50.4000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000(3 行受影响)
*/