Sql Server里巧用Case将多行显示的数据合并为一行显示

最新推荐文章于 2024-01-12 10:51:58 发布

weixin_33686714

最新推荐文章于 2024-01-12 10:51:58 发布

阅读量245

点赞数

昨晚在CSDN论坛上看到有某个人问了类似这样的一个问题，现有三个数据表,分别是学生表,课程表,成绩表。它们的结构与样例数据如下：

学生表：
学生Id    姓名
1             张三
2             李四
3             王五

课程表：
课程Id    课程名
1             语文
2             化学
3             外语
4             物理

成绩表：
学生Id     课程Id        成绩
1               1                 60
1               2                 70
1               3                 65
1               4                 90
2               1                 80
2               2                 65
2               3                 85
2               4                 80
3               1                 50
3               2                 75
3               3                 85
3               4                 60

现要求在一行中输出每个学生的所有课程的成绩单，显示样例格式如下：
姓名        语文        化学       外语        物理
张三        60            70           65            90
李四        80            65           85            80
王五        50            75           85            60

当大家看到这样的题目时会优先考虑到的是怎样的一条SQL语句呢？嵌套Select？对，在将行转换为列时，也许这种方法是最优先考虑到（或者你是高手，所以不是优先考虑到这个而是其它)，所以我最开始也写出了下面这条语句：

SELECT B.姓名,

( SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID = 课程.课程ID WHERE 成绩表.学生ID = B.学生ID AND 课程.课程名 = ' 语文 ' ) AS 语文,

( SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID = 课程.课程ID WHERE 成绩表.学生ID = B.学生ID AND 课程.课程名 = ' 化学 ' ) AS 化学,

( SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID = 课程.课程ID WHERE 成绩表.学生ID = B.学生ID AND 课程.课程名 = ' 外语 ' ) AS 外语,

( SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID = 课程.课程ID WHERE 成绩表.学生ID = B.学生ID AND 课程.课程名 = ' 物理 ' ) AS 物理

FROM 学生 B

这样我们的目的是达到了，但后来我又想了一下，因为我们要的数据其实都在 成绩表里，只不过现有的是用行来存放，那我们怎么将它转换为列显示呢？嗯，这也许就要搬出聚合函数加Case条件来处理了！最终的SQL语句如下：

SELECT 姓名,

MAX ( CASE 课程名 WHEN ' 语文 ' THEN 成绩 ELSE 0 END ) AS 语文,

MAX ( CASE 课程名 WHEN ' 化学 ' THEN 成绩 ELSE 0 END ) AS 化学,

MAX ( CASE 课程名 WHEN ' 外语 ' THEN 成绩 ELSE 0 END ) AS 外语,

MAX ( CASE 课程名 WHEN ' 物理 ' THEN 成绩 ELSE 0 END ) AS 物理

FROM ( SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D

INNER JOIN 学生 B ON B.学生ID = D.学生ID

INNER JOIN 课程 C ON C.课程ID = D.课程ID) AS TMP GROUP BY 姓名

运行后，也是可以得到正确的数据，下面给出测试代码，大家可以直接在SQL查询分析器里运行

CREATE TABLE 学生 (学生ID INT , 姓名 VARCHAR ( 20 ))

CREATE TABLE 课程 (课程ID INT , 课程名 VARCHAR ( 20 ))

CREATE TABLE 成绩表 (学生ID INT , 课程ID INT , 成绩 INT )

INSERT INTO 学生

SELECT 1 , ' 张三 ' UNION ALL

SELECT 2 , ' 李四 ' UNION ALL

SELECT 3 , ' 王五 '

INSERT INTO 课程

SELECT 1 , ' 语文 ' UNION ALL

SELECT 2 , ' 化学 ' UNION ALL

SELECT 3 , ' 外语 ' UNION ALL

SELECT 4 , ' 物理 '

INSERT INTO 成绩表

SELECT 1 , 1 , 60 UNION ALL

SELECT 1 , 2 , 70 UNION ALL

SELECT 1 , 3 , 65 UNION ALL

SELECT 1 , 4 , 90 UNION ALL

SELECT 2 , 1 , 80 UNION ALL

SELECT 2 , 2 , 65 UNION ALL

SELECT 2 , 3 , 85 UNION ALL

SELECT 2 , 4 , 80 UNION ALL

SELECT 3 , 1 , 50 UNION ALL

SELECT 3 , 2 , 75 UNION ALL

SELECT 3 , 3 , 85 UNION ALL

SELECT 3 , 4 , 60

-- 方法一