假如我有一个这样的数据表日期 姓名 语文 数学 英语 体育
2009-09-20 a 60 70 80 90
2009-09-01 b 100 100 100 100
2009-09-18 c 30 20 40 0
2009-09-20 d 50 60 70 80
2009-09-01 e 100 100 100 100
2009-09-18 f 30 20 40 0
请问用sql语句如何能得到我下面的格式数据:条件假如我要日期为20号的,20号其他同学没数据的显示为空或者0.
科目 a b c d e f 日期
语文 60 50 2009-09-20
数据 70 60 2009-09-20
英语 80 70 2009-09-20
体育 90 80 2009-09-20
2009-09-20 a 60 70 80 90
2009-09-01 b 100 100 100 100
2009-09-18 c 30 20 40 0
2009-09-20 d 50 60 70 80
2009-09-01 e 100 100 100 100
2009-09-18 f 30 20 40 0
请问用sql语句如何能得到我下面的格式数据:条件假如我要日期为20号的,20号其他同学没数据的显示为空或者0.
科目 a b c d e f 日期
语文 60 50 2009-09-20
数据 70 60 2009-09-20
英语 80 70 2009-09-20
体育 90 80 2009-09-20
insert into @t
select 1, 4.7 ,5.5, 6.3, 7.7, 8.9
union all select 2, 5.7 ,6.5, 7.3, 8.7, 9.9
union all select 3, 6.7 ,7.5, 8.3, 9.7, 10.9select '1' = a.a,'2' =b.a,'3'=c.a
from @t a
inner join @t b on a.id < b.id
inner join @t c on a.id < b.id and b.id<c.id
union all
select '1' = a.b,'2' =b.b,'3'=c.b
from @t a
inner join @t b on a.id < b.id
inner join @t c on a.id < b.id and b.id<c.id
union all
select '1' = a.c,'2' =b.c,'3'=c.c
from @t a
inner join @t b on a.id < b.id
inner join @t c on a.id < b.id and b.id<c.id
union all
select '1' = a.d,'2' =b.d,'3'=c.d
from @t a
inner join @t b on a.id < b.id
inner join @t c on a.id < b.id and b.id<c.id
union all
select '1' = a.e,'2' =b.e,'3'=c.e
from @t a
inner join @t b on a.id < b.id
inner join @t c on a.id < b.id and b.id<c.id 1 2 3
-------- -------- --------
4.70 5.70 6.70
5.50 6.50 7.50
6.30 7.30 8.30
7.70 8.70 9.70
8.90 9.90 10.90(所影响的行数为 5 行)
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-20 10:40:09
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([日期] DATETIME,[姓名] NVARCHAR(10),[语文] INT,[数学] INT,[英语] INT,[体育] INT)
INSERT [tb]
SELECT N'2009-09-20','a',60,70,80,90 UNION ALL
SELECT N'2009-09-01','b',100,100,100,100 UNION ALL
SELECT N'2009-09-18','c',30,20,40,0 UNION ALL
SELECT N'2009-09-20','d',50,60,70,80 UNION ALL
SELECT N'2009-09-01','e',100,100,100,100 UNION ALL
SELECT N'2009-09-18','f',30,20,40,0
GO
--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @s VARCHAR(MAX),@d VARCHAR(10)
SELECT @s=ISNULL(@s+',','')+姓名 FROM tb
SET @d='2009-09-20'
EXEC('
SELECT 科目,'+@s+',日期
FROM (SELECT * FROM tb WHERE 日期='''+@d+''') a
UNPIVOT(分数 FOR 科目 IN(语文,数学,英语,体育)) b
PIVOT(MAX(分数) FOR 姓名 IN('+@s+')) c ')
/*
科目 a b c d e f 日期
数学 70 NULL NULL 60 NULL NULL 2009-09-20 00:00:00.000
体育 90 NULL NULL 80 NULL NULL 2009-09-20 00:00:00.000
英语 80 NULL NULL 70 NULL NULL 2009-09-20 00:00:00.000
语文 60 NULL NULL 50 NULL NULL 2009-09-20 00:00:00.000
*/