行列转换方面的问题
表1：
学生ID 课程 成绩
1 英语 60
1 数学 66
1 体育 77
1 网络基础 88
1 计算机 99
2 英语 55
2 数学 66
2 体育 77
2 网络基础 88
2 计算机 67
3 英语 55
3 数学 66
3 体育 77
3 网络基础 88
3 计算机 67
表2：
ID 学生ID 理论考试 操作考试
1 1 66 77
1 2 87 67
1 3 65 76
上次我提问怎么将表1的查询出下面的结果：
学生ID 英语 数学 体育 网络基础 计算机
...
有个大神你给了我这个代码（我根据数据库情况改了字段名）
declare @sql varchar(8000)
set @sql = 'select XYID '
select @sql = @sql + ' , max(case PXKC when ''' + PXKC + ''' then KCCJ else 0 end) [' + PXKC+ ']'
from (select distinct PXKC from tblXYCJ) as a
set @sql = @sql + ' from tblXYCJ group by XYID'
exec(@sql)
现在我想讲上面代码的结果和表2联合起来查询得到下面的结果：
学生ID 英语 数学 体育 网络基础 计算机 理论考试 操作考试 综合成绩（综合成绩是前面各科成绩之和求平均）
...
[解决办法]
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2011-12-23 14:55:07-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([学生ID] int,[课程] varchar(8),[成绩] int)insert [a]select 1,'英语',60 union allselect 1,'数学',66 union allselect 1,'体育',77 union allselect 1,'网络基础',88 union allselect 1,'计算机',99 union allselect 2,'英语',55 union allselect 2,'数学',66 union allselect 2,'体育',77 union allselect 2,'网络基础',88 union allselect 2,'计算机',67 union allselect 3,'英语',55 union allselect 3,'数学',66 union allselect 3,'体育',77 union allselect 3,'网络基础',88 union allselect 3,'计算机',67--> 测试数据：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int,[学生ID] int,[理论考试] int,[操作考试] int)insert [b]select 1,1,66,77 union allselect 1,2,87,67 union allselect 1,3,65,76--------------开始查询--------------------------declare @sql varchar(8000)set @sql = 'select a.学生ID,b.理论考试,b.操作考试'select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 成绩 else 0 end) [' + 课程+ ']'from (select distinct 课程 from a) as aset @sql = @sql + ',avg(a.成绩) as 综合成绩 from a join b on a.学生ID=b.学生ID group by a.学生ID,a.学生ID,b.理论考试,b.操作考试'exec(@sql)----------------结果----------------------------/* 学生ID 理论考试 操作考试 计算机 数学 体育 网络基础 英语 综合成绩----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------1 66 77 99 66 77 88 60 782 87 67 67 66 77 88 55 703 65 76 67 66 77 88 55 70(3 行受影响)*/
[解决办法]
- SQL code
create table tab1(学生ID int, 课程 varchar(10), 成绩 int)insert into tab1select 1, '英语', 60 union allselect 1, '数学', 66 union allselect 1, '体育', 77 union allselect 1, '网络基础', 88 union allselect 1, '计算机', 99 union allselect 2, '英语', 55 union allselect 2, '数学', 66 union allselect 2, '体育', 77 union allselect 2, '网络基础', 88 union allselect 2, '计算机', 67 union allselect 3, '英语', 55 union allselect 3, '数学', 66 union allselect 3, '体育', 77 union allselect 3, '网络基础', 88 union allselect 3, '计算机', 67create table tab2(ID int, 学生ID int, 理论考试 int, 操作考试 int)insert into tab2select 1, 1, 66, 77 union allselect 1, 2, 87, 67 union allselect 1, 3, 65, 76declare @sql varchar(5000),@x intselect @sql='with t as (select 学生ID,'select @sql=@sql+'sum(case when 课程='''+课程+''' then 成绩 else 0 end) ['+课程+'],'from (select distinct 课程 from tab1) torder by 课程 descselect @sql=left(@sql,len(@sql)-1)+' from tab1 group by 学生ID)'select @x=count(distinct 课程)+2 from tab1select @sql=@sql+'select a.*,b.理论考试,b.操作考试,(c.ass+b.理论考试+b.操作考试)/'+cast(@x as varchar)+' ''综合成绩''from t ainner join tab2 b on a.学生ID=b.学生IDinner join (select 学生ID,sum(成绩) ass from tab1 group by 学生ID) con a.学生ID=c.学生ID'exec(@sql)学生ID 英语 网络基础 体育 数学 计算机 理论考试 操作考试 综合成绩----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------1 60 88 77 66 99 66 77 762 55 88 77 66 67 87 67 723 55 88 77 66 67 65 76 70(3 row(s) affected)