当前位置:   article > 正文

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

sql 多表全连接 case when结果集从第一行开始显示数据

昨晚在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?对,在将行转换为列时,也许这种方法是最优先考虑到(或者你是高手,所以不是优先考虑到这个而是其它shades_smile.gif),所以我最开始也写出了下面这条语句:

None.gif SELECT  B.姓名,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 语文 '  )  AS  语文,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 化学 '  )  AS  化学,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 外语 '  )  AS  外语,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 物理 '  )  AS  物理
None.gif
FROM  学生 B
None.gif

这样我们的目的是达到了,但后来我又想了一下,因为我们要的数据其实都在 成绩表里,只不过现有的是用行来存放,那我们怎么将它转换为列显示呢?嗯,这也许就要搬出聚合函数加Case条件来处理了!最终的SQL语句如下:
None.gif SELECT  姓名,
None.gif
MAX ( CASE  课程名  WHEN   ' 语文 '   THEN  成绩  ELSE   0   END AS  语文,
None.gif
MAX ( CASE  课程名  WHEN   ' 化学 '   THEN  成绩  ELSE   0   END AS  化学,
None.gif
MAX ( CASE  课程名  WHEN   ' 外语 '   THEN  成绩  ELSE   0   END AS  外语,
None.gif
MAX ( CASE  课程名  WHEN   ' 物理 '   THEN  成绩  ELSE   0   END AS  物理
None.gif
FROM  ( SELECT  B.姓名,C.课程名,D.成绩  FROM  成绩表 D 
None.gif
INNER   JOIN  学生 B  ON  B.学生ID = D.学生ID 
None.gif
INNER   JOIN  课程 C  ON  C.课程ID = D.课程ID)  AS  TMP  GROUP   BY  姓名
None.gif

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

None.gif CREATE   TABLE  学生 (学生ID  INT , 姓名  VARCHAR ( 20 ))
None.gif
CREATE   TABLE  课程 (课程ID  INT , 课程名  VARCHAR ( 20 ))
None.gif
CREATE   TABLE  成绩表 (学生ID  INT , 课程ID  INT , 成绩  INT )
None.gif
None.gif
INSERT   INTO  学生
None.gif
SELECT   1 , ' 张三 '   UNION   ALL
None.gif
SELECT   2 , ' 李四 '   UNION   ALL
None.gif
SELECT   3 , ' 王五 '
None.gif
None.gif
INSERT   INTO  课程
None.gif
SELECT   1 , ' 语文 '   UNION   ALL
None.gif
SELECT   2 , ' 化学 '   UNION   ALL
None.gif
SELECT   3 , ' 外语 '   UNION   ALL
None.gif
SELECT   4 , ' 物理 '
None.gif
None.gif
INSERT   INTO  成绩表
None.gif
SELECT   1 , 1 , 60   UNION   ALL
None.gif
SELECT   1 , 2 , 70   UNION   ALL
None.gif
SELECT   1 , 3 , 65   UNION   ALL
None.gif
SELECT   1 , 4 , 90   UNION   ALL
None.gif
SELECT   2 , 1 , 80   UNION   ALL
None.gif
SELECT   2 , 2 , 65   UNION   ALL
None.gif
SELECT   2 , 3 , 85   UNION   ALL
None.gif
SELECT   2 , 4 , 80   UNION   ALL
None.gif
SELECT   3 , 1 , 50   UNION   ALL
None.gif
SELECT   3 , 2 , 75   UNION   ALL
None.gif
SELECT   3 , 3 , 85   UNION   ALL
None.gif
SELECT   3 , 4 , 60
None.gif
None.gif
-- 方法一
None.gif
SELECT  B.姓名,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 语文 '  )  AS  语文,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 化学 '  )  AS  化学,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 外语 '  )  AS  外语,
None.gif(
SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 物理 '  )  AS  物理
None.gif
FROM  学生 B
None.gif
None.gif
-- 方法二
None.gif
SELECT  姓名,
None.gif
MAX ( CASE  课程名  WHEN   ' 语文 '   THEN  成绩  ELSE   0   END AS  语文,
None.gif
MAX ( CASE  课程名  WHEN   ' 化学 '   THEN  成绩  ELSE   0   END AS  化学,
None.gif
MAX ( CASE  课程名  WHEN   ' 外语 '   THEN  成绩  ELSE   0   END AS  外语,
None.gif
MAX ( CASE  课程名  WHEN   ' 物理 '   THEN  成绩  ELSE   0   END AS  物理
None.gif
FROM  ( SELECT  B.姓名,C.课程名,D.成绩  FROM  成绩表 D 
None.gif
INNER   JOIN  学生 B  ON  B.学生ID = D.学生ID 
None.gif
INNER   JOIN  课程 C  ON  C.课程ID = D.课程ID)  AS  TMP  GROUP   BY  姓名
None.gif
None.gif
None.gif
DROP   TABLE  学生
None.gif
DROP   TABLE  课程
None.gif
DROP   TABLE  成绩表

PS:用嵌套SELECT与用聚合函数加Case两者的效率如何,我没有测试,各位有兴趣的可测试一下 shades_smile.gif
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Guff_9hys/article/detail/766550
推荐阅读
相关标签
  

闽ICP备14008679号