create database test
use test
--一：
if object_id('student') is not null drop table student
create table student
(
id int identity(1,1),
stuname varchar(20),
kecheng varchar(20),
fenshu int,
xueqi varchar(20)
)
insert into student
select '张三','语文',81,'04年第一学期' union all
select '张三','数学',90,'04年第一学期' union all
select '张三','英语',70,'04年第一学期' union all
select '李四','语文',81,'04年第一学期' union all
select '李四','数学',81,'04年第一学期' union all
select '李四','英语',94,'04年第一学期' union all
select '王五','语文',100,'04年第一学期' union all
select '王五','数学',81,'04年第一学期' union all
select '王五','英语',90,'04年第一学期'
select * from student
/*
id stuname kecheng fenshu xueqi
----------- -------------------- -------------------- ----------- --------------------
1 张三 语文 81 04年第一学期
2 张三 数学 90 04年第一学期
3 张三 英语 70 04年第一学期
4 李四 语文 81 04年第一学期
5 李四 数学 81 04年第一学期
6 李四 英语 94 04年第一学期
7 王五 语文 100 04年第一学期
8 王五 数学 81 04年第一学期
9 王五 英语 90 04年第一学期
*/
--要求1：取得所有成绩为优的学生的名单(优的标准是-分数大于等于80)
select distinct t.stuname from student t where not exists
(select 1 from student where stuname=t.stuname and fenshu<80)
/*
stuname
--------------------
李四
王五
*/
--要求2：成绩最好的学生(成绩最好的标准是-平均分最高)
select top 1 stuname,aravge from
(select distinct stuname,aravge=(sum(fenshu)/3) from student group by stuname) b order by aravge desc
/*
stuname aravge
-------------------- -----------
王五 90
*/
--要求3：得到如下所示效果(即行列转换)
-- 学生名称 语 文 数 学 英 语 学 期
-- 张三 81 90 70 04年第一学期
-- 李四 81 81 94 04年第一学期
-- 王五 100 81 90 04年第一学期
--1.
select stuname as '学生姓名',xueqi as '学期',max(case when kecheng='语文' then fenshu else 0 end) as '语文',
max(case when kecheng='数学' then fenshu else 0 end) as '数学',
max(case when kecheng='英语' then fenshu else 0 end) as '英语'
from student group by stuname,xueqi
--结果：
--学生姓名 学期 语文 数学 英语
---------------------- -------------------- ----------- ----------- -----------
--李四 04年第一学期 81 81 94
--王五 04年第一学期 100 81 90
--张三 04年第一学期 81 90 70
--2.
select stuname as '学生姓名',xueqi as '学期',max(case when kecheng='语文' then fenshu else 0 end) as '语文',
max(case when kecheng='数学' then fenshu else 0 end) as '数学',
max(case when kecheng='英语' then fenshu else 0 end) as '英语'
from student group by stuname,xueqi, [id] order by [id] asc
--结果：
--学生姓名 学期 语文 数学 英语
---------------------- -------------------- ----------- ----------- -----------
--张三 04年第一学期 81 0 0
--张三 04年第一学期 0 90 0
--张三 04年第一学期 0 0 70
--李四 04年第一学期 81 0 0
--李四 04年第一学期 0 81 0
--李四 04年第一学期 0 0 94
--王五 04年第一学期 100 0 0
--王五 04年第一学期 0 81 0
--王五 04年第一学期 0 0 90
--二：
--动态课程行转列
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] int,[姓名] varchar(10),[课程] varchar(10),[成绩] int)
insert [tb] select 1,'李某','数学',60
union all select 1,'李某','语文',80
union all select 1,'李某','英语',50
union all select 2,'张某','数学',40
union all select 2,'张某','语文',60
union all select 2,'张某','英语',70
go
select * from tb
--表如下：
--编号 姓名 课程 成绩
------------- ---------- ---------- -----------
--1 李某 数学 60
--1 李某 语文 80
--1 李某 英语 50
--2 张某 数学 40
--2 张某 语文 60
--2 张某 英语 70
--存储过程：
if object_id('[sp_test]') is not null drop proc [sp_test]
go
create proc sp_test
as
set nocount on
declare @s varchar(8000)
set @s='select 编号,姓名'
select @s=@s+',max(case when 课程='''+课程+''' then 成绩 else 0 end)['+课程+']'
from (select distinct 课程 from tb) t
set @s=@s+' from tb group by 编号,姓名'
exec(@s)
set nocount off
go
--调用:
exec sp_test
/*
编号 姓名 数学 英语 语文
----------- ---------- ----------- ----------- -----------
1 李某 60 50 80
2 张某 40 70 60
代码
