sql server多行数据合并显示
现有三个数据表,分别是学生表,课程表,成绩表。它们的结构与样例数据如下：
学生表：
xno xname
1 小王
2 小黑
课程表：
kno kname
1 语文
2 数学
成绩表：
cno ckno cf
1 1 86.5
1 2 98.86
2 1 86.7
2 2 86.9
create table 学生表
(
xno int ,
xname varchar(20)
)
go
insert into dbo.学生表
select 1,'小王'
union all
select 2,'小黑'
create table 课程表
(
kno int ,
kname varchar(20)
)
go
insert into 课程表
select 1,'语文'
union all
select 2,'数学'
create table 成绩表
(
cno int,
ckno int,
cf float
)
alter table 成绩表
alter column cf
insert into 成绩表
select 1,1,86.5
insert into 成绩表
select 1,2,98.86
union all
select 2,1,86.7
union all
select 2,2,86.9
select * from 学生表
select * from 课程表
select * from 成绩表
(
xno int ,
xname varchar(20)
)
go
insert into dbo.学生表
select 1,'小王'
union all
select 2,'小黑'
create table 课程表
(
kno int ,
kname varchar(20)
)
go
insert into 课程表
select 1,'语文'
union all
select 2,'数学'
create table 成绩表
(
cno int,
ckno int,
cf float
)
alter table 成绩表
alter column cf
insert into 成绩表
select 1,1,86.5
insert into 成绩表
select 1,2,98.86
union all
select 2,1,86.7
union all
select 2,2,86.9
select * from 学生表
select * from 课程表
select * from 成绩表
现要求在一行中输出每个学生的所有课程的成绩单，显示样例格式如下：
姓名 语文 数学
小王 60 70
小黑 80 65
当大家看到这样的题目时会优先考虑到的是怎样的一条SQL语句呢？嵌套Select？对，在将行转换为列时，也许这种方法是最优先考虑到
SELECT B.xname,
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='语文' ) AS 语文,
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='数学' ) AS 数学
FROM 学生表 B
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='语文' ) AS 语文,
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='数学' ) AS 数学
FROM 学生表 B
这样我们的目的是达到了，但后来我又想了一下，因为我们要的数据其实都在成绩表里，只不过现有的是用行来存放，那我们怎么将它转换为列显示呢？嗯，这也许就要搬出聚合函数加Case条件来处理了！最终的SQL语句如下：
SELECT xname,
MAX(CASE kname WHEN '语文' THEN cf ELSE 0 END) AS 语文,
MAX(CASE kname WHEN '数学' THEN cf ELSE 0 END) AS 数学
FROM (SELECT B.xname,C.kname,D.cf FROM 成绩表 D
INNER JOIN 学生表 B ON B.xno=D.cno
INNER JOIN 课程表 C ON C.kno=D.ckno) AS ZH GROUP BY xname
MAX(CASE kname WHEN '语文' THEN cf ELSE 0 END) AS 语文,
MAX(CASE kname WHEN '数学' THEN cf ELSE 0 END) AS 数学
FROM (SELECT B.xname,C.kname,D.cf FROM 成绩表 D
INNER JOIN 学生表 B ON B.xno=D.cno
INNER JOIN 课程表 C ON C.kno=D.ckno) AS ZH GROUP BY xname
运行后，也是可以得到正确的数据，下面给出测试代码，大家可以直接在SQL查询分析器里运行
方法一：
SELECT B.xname,
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='语文' ) AS 语文,
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='数学' ) AS 数学
FROM 学生表 B
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='语文' ) AS 语文,
(SELECT cf FROM 成绩表 INNER JOIN 课程表 ON 成绩表.ckno=课程表.kno WHERE 成绩表.cno=B.xno AND 课程表.kname='数学' ) AS 数学
FROM 学生表 B
方法二：
SELECT xname,
MAX(CASE kname WHEN '语文' THEN cf ELSE 0 END) AS 语文,
MAX(CASE kname WHEN '数学' THEN cf ELSE 0 END) AS 数学
FROM (SELECT B.xname,C.kname,D.cf FROM 成绩表 D
INNER JOIN 学生表 B ON B.xno=D.cno
INNER JOIN 课程表 C ON C.kno=D.ckno) AS ZH GROUP BY xname
MAX(CASE kname WHEN '语文' THEN cf ELSE 0 END) AS 语文,
MAX(CASE kname WHEN '数学' THEN cf ELSE 0 END) AS 数学
FROM (SELECT B.xname,C.kname,D.cf FROM 成绩表 D
INNER JOIN 学生表 B ON B.xno=D.cno
INNER JOIN 课程表 C ON C.kno=D.ckno) AS ZH GROUP BY xname
DROP TABLE 学生表
DROP TABLE 课程表
DROP TABLE 成绩表
