Oracle和Mysql和mssql中将多行记录合并为一行

Bertha 。 2021-09-15 13:02 727阅读 0赞

### **测试表与测试数据**  CREATE TABLE TestTitle(  name VARCHAR(10),  title VARCHAR(10)  );       INSERT INTO TestTitle VALUES ('张三', '程序员');  INSERT INTO TestTitle VALUES ('张三', '系统管理员');  INSERT INTO TestTitle VALUES ('张三', '网络管理员');  INSERT INTO TestTitle VALUES ('李四', '项目经理');  INSERT INTO TestTitle VALUES ('李四', '系统分析员');       **要求**  对于测试数据，要求查询结果为：  张三程序员，系统管理员，网络管理员  李四项目经理，系统分析员  这种结构的结果。       **对于Oracle：**  SELECT name,  WMSYS.WM\_CONCAT(title) AS allTitle   FROM TestTitle       **对于Mysql：**  SELECT name, group\_concat( title ORDER BY title separator "," )  AS title    FROM TestTitle   GROUP BY name   ===================================================================================================================  ms sql  昨晚在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][]         ###

[shades_smile.gif]: /images/20210726/6f710a4e60a041e281dc34a0a63ee94c.png
[None.gif]: http://www.cnblogs.com/Images/OutliningIndicators/None.gif