日期:2014-05-17 浏览次数:20827 次
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-28 15:56:05
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[科目表]
if object_id('[科目表]') is not null drop table [科目表]
go
create table [科目表]([科目ID] int,[科目名称] varchar(4))
insert [科目表]
select 1,'数学' union all
select 2,'语文' union all
select 3,'物理' union all
select 4,'化学'
--> 测试数据:[成绩表]
if object_id('[成绩表]') is not null drop table [成绩表]
go
create table [成绩表]([学生id] int,[科目id] int,[考试期数] int,[成绩] int)
insert [成绩表]
select 1,1,1,80 union all
select 1,2,1,90 union all
select 1,3,1,60 union all
select 2,1,1,66 union all
select 2,1,1,78 union all
select 1,1,2,70 union all
select 2,1,2,67
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(科目名称)+'=max(case when [科目名称]='+quotename(科目名称,'''')+' then [成绩] else 0 end)'
from (select 学生id,a.科目id,考试期数,成绩,科目名称
from [成绩表] a LEFT JOIN [科目表] b ON a.科目id=b.科目id ) a group by 科目名称
exec('select [学生ID],考试期数'+@s+' from (select 学生id,a.科目id,考试期数,成绩,科目名称
from [成绩表] a LEFT JOIN [科目表] b ON a.科目id=b.科目id) a
group by [学生ID],考试期数')
declare @s1 nvarchar(4000)
set @s1=''
Select @s1=@s1+','+quotename('期数'+CONVERT(CHAR(1),考试期数))+'=max(case when [考试期数]='+quotename(考试期数,'''')+' then [成绩] else&nb