Server里巧用Case将多行突显的数据统一为一行展现

昨夜在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?对,在将行转换为列时,也许那种办法是最优先考虑到(或者您是高手,所以不是先行考虑到这些而是此外化学答案 1),所以我最早先也写出了上面那条语句:

化学答案 2SELECT B.姓名,
化学答案 3(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’语文’ ) AS 语文,
化学答案 4(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’化学’ ) AS 化学,
化学答案 5(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’外语’ ) AS 外语,
化学答案 6(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’物理’ ) AS 物理
化学答案 7FROM 学生 B
化学答案 8

那般我们的目标是高达了,但新兴自己又想了瞬间,因为大家要的数额实际上都在成绩表里,只但是现有的是用行来存放在,那大家怎么将它转换为列呈现呢?嗯,那或许就要搬出聚合函数加Case条件来拍卖了!最后的SQL语句如下:

化学答案 9SELECT 姓名,
化学答案 10MAX(CASE 课程名 WHEN ‘语文’ THEN 成绩 ELSE 0 END) AS 语文,
化学答案 11MAX(CASE 课程名 WHEN ‘化学’ THEN 成绩 ELSE 0 END) AS 化学,
化学答案 12MAX(CASE 课程名 WHEN ‘外语’ THEN 成绩 ELSE 0 END) AS 外语,
化学答案 13MAX(CASE 课程名 WHEN ‘物理’ THEN 成绩 ELSE 0 END) AS 物理
化学答案 14FROM (SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D 
化学答案 15INNER JOIN 学生 B ON B.学生ID=D.学生ID 
化学答案 16INNER JOIN 课程 C ON C.课程ID=D.课程ID) AS TMP GROUP BY 姓名
化学答案 17

运作后,也是可以收获正确的数目,上边给出测试代码,大家可以一向在SQL查询分析器里运行

化学答案 18CREATE TABLE 学生 (学生ID INT, 姓名 VARCHAR(20))
化学答案 19CREATE TABLE 课程 (课程ID INT, 课程名 VARCHAR(20))
化学答案 20CREATE TABLE 成绩表 (学生ID INT, 课程ID INT, 成绩 INT)
化学答案 21
化学答案 22INSERT INTO 学生
化学答案 23SELECT 1,’张三’ UNION ALL
化学答案 24SELECT 2,’李四’ UNION ALL
化学答案 25SELECT 3,’王五’
化学答案 26
化学答案 27INSERT INTO 课程
化学答案 28SELECT 1,’语文’ UNION ALL
化学答案 29SELECT 2,’化学’ UNION ALL
化学答案 30SELECT 3,’外语’ UNION ALL
化学答案 31SELECT 4,’物理’
化学答案 32
化学答案 33INSERT INTO 成绩表
化学答案 34SELECT 1,1,60 UNION ALL
化学答案 35SELECT 1,2,70 UNION ALL
化学答案 36SELECT 1,3,65 UNION ALL
化学答案 37SELECT 1,4,90 UNION ALL
化学答案 38SELECT 2,1,80 UNION ALL
化学答案 39SELECT 2,2,65 UNION ALL
化学答案 40SELECT 2,3,85 UNION ALL
化学答案 41SELECT 2,4,80 UNION ALL
化学答案 42SELECT 3,1,50 UNION ALL
化学答案 43SELECT 3,2,75 UNION ALL
化学答案 44SELECT 3,3,85 UNION ALL
化学答案 45SELECT 3,4,60
化学答案 46
化学答案 47–方法一
化学答案 48SELECT B.姓名,
化学答案 49(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’语文’ ) AS 语文,
化学答案 50(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’化学’ ) AS 化学,
化学答案 51(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’外语’ ) AS 外语,
化学答案 52化学答案,(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名=’物理’ ) AS 物理
化学答案 53FROM 学生 B
化学答案 54
化学答案 55–方法二
化学答案 56SELECT 姓名,
化学答案 57MAX(CASE 课程名 WHEN ‘语文’ THEN 成绩 ELSE 0 END) AS 语文,
化学答案 58MAX(CASE 课程名 WHEN ‘化学’ THEN 成绩 ELSE 0 END) AS 化学,
化学答案 59MAX(CASE 课程名 WHEN ‘外语’ THEN 成绩 ELSE 0 END) AS 外语,
化学答案 60MAX(CASE 课程名 WHEN ‘物理’ THEN 成绩 ELSE 0 END) AS 物理
化学答案 61FROM (SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D 
化学答案 62INNER JOIN 学生 B ON B.学生ID=D.学生ID 
化学答案 63INNER JOIN 课程 C ON C.课程ID=D.课程ID) AS TMP GROUP BY 姓名
化学答案 64
化学答案 65
化学答案 66DROP TABLE 学生
化学答案 67DROP TABLE 课程
化学答案 68DROP TABLE 成绩表

PS:用嵌套SELECT与用聚合函数加Case两者的频率怎么样,我尚未测试,各位有趣味的可测试一下化学答案 69

admin

网站地图xml地图