mobile.365-838.comServer里巧用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?对,在将行转换为列时,也许那种措施是最优先考虑到(或者你是一把手,所以不是事先考虑到那个而是其余mobile.365-838.com 1),所以自己最起头也写出了下边那条语句:

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

诸如此类我们的目标是高达了,但后来我又想了眨眼间间,因为大家要的数额实际上都在成绩表里,只但是现有的是用行来存放在,这大家怎么将它转换为列展现呢?嗯,那也许就要搬出聚合函数加Case条件来处理了!最后的SQL语句如下:

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

运行后,也是足以获取不错的数量,下边给出测试代码,大家可以直接在SQL查询分析器里运行

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

PS:用嵌套SELECT与用聚合函数加Case两者的频率怎么样,我从不测试,各位有趣味的可测试一下mobile.365-838.com 69

admin

网站地图xml地图