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地图