22,210
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-25 16:14:35
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[T_Exam_Subject]
if object_id('[T_Exam_Subject]') is not null drop table [T_Exam_Subject]
go
create table [T_Exam_Subject]([id] int,[科目名称] varchar(4))
insert [T_Exam_Subject]
select 1,'理论' union all
select 2,'实践'
--> 测试数据:[T_Exam_Student]
if object_id('[T_Exam_Student]') is not null drop table [T_Exam_Student]
go
create table [T_Exam_Student]([id] int,[考生名称] varchar(8),[性别] varchar(2),[准考证号] bigint,[身份证号] int,[考点ID] int,[考期ID] int)
insert [T_Exam_Student]
select 5,'测试考生','男',20140801001001,312312,1,1
--> 测试数据:[T_Exam_Results]
if object_id('[T_Exam_Results]') is not null drop table [T_Exam_Results]
go
create table [T_Exam_Results]([id] int,[学生ID] int,[科目ID] int,[分数] int)
insert [T_Exam_Results]
select 2,5,2,90 union all
select 9,5,1,100
--> 测试数据:[T_Exam_Years]
if object_id('[T_Exam_Years]') is not null drop table [T_Exam_Years]
go
create table [T_Exam_Years]([id] int,[考期名] varchar(10),[isopen] int)
insert [T_Exam_Years]
select 1,'2014年(下)',1
--> 测试数据:[T_Exam_Point]
if object_id('[T_Exam_Point]') is not null drop table [T_Exam_Point]
go
create table [T_Exam_Point]([id] int,[考点名称] varchar(8))
insert [T_Exam_Point]
select 1,'测试考点'
--------------开始查询--------------------------
IF OBJECT_ID('tempdb..#t','u') IS NOT NULL
DROP TABLE #t
select a.id AS 考生ID,a.考生名称,a.性别 ,a.准考证号,a.身份证号 ,d.考点名称 AS 所属考点,
e.考期名 AS 报考考期,c.科目名称,b.分数 INTO #t
from [T_Exam_Student] a INNER JOIN [T_Exam_Results] b ON a.id=b.学生ID
INNER JOIN T_Exam_Subject c ON c.id=b.科目id
INNER JOIN T_Exam_Point d ON a.考点ID=d.ID
INNER JOIN T_Exam_Years E ON A.考期ID =e.id
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(科目名称)+'=max(case when [科目名称]='+quotename(科目名称,'''')+' then [分数] else 0 end)'
from #t group by 科目名称
exec('select 考生ID,考生名称,性别,准考证号,身份证号,所属考点,报考考期'+@s+' from #t group by 考生ID,考生名称,性别,准考证号,身份证号,所属考点,报考考期')
----------------结果----------------------------
/*
考生ID 考生名称 性别 准考证号 身份证号 所属考点 报考考期 理论 实践
----------- -------- ---- -------------------- ----------- -------- ---------- ----------- -----------
5 测试考生 男 20140801001001 312312 测试考点 2014年(下) 100 90
*/