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

李维亮

发布于 2021-07-09 03:18:17

1.4K00

代码可运行

文章被收录于专栏：李维亮的博客李维亮的博客

运行总次数：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？对，在将行转换为列时，也许这种方法是最优先考虑到（或者你是高手，所以不是优先考虑到这个而是其它)，所以我最开始也写出了下面这条语句：

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

这样我们的目的是达到了，但后来我又想了一下，因为我们要的数据其实都在成绩表里，只不过现有的是用行来存放，那我们怎么将它转换为列显示呢？嗯，这也许就要搬出聚合函数加Case条件来处理了！最终的SQL语句如下：

SELECT 姓名,
MAX(CASE 课程名 WHEN '语文' THEN 成绩 ELSE 0 END) AS 语文,
MAX(CASE 课程名 WHEN '化学' THEN 成绩 ELSE 0 END) AS 化学,
MAX(CASE 课程名 WHEN '外语' THEN 成绩 ELSE 0 END) AS 外语,
MAX(CASE 课程名 WHEN '物理' THEN 成绩 ELSE 0 END) AS 物理
FROM (SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D 
INNER JOIN 学生 B ON B.学生ID=D.学生ID 
INNER JOIN 课程 C ON C.课程ID=D.课程ID) AS TMP GROUP BY 姓名

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

CREATE TABLE 学生 (学生ID INT, 姓名 VARCHAR(20))
CREATE TABLE 课程 (课程ID INT, 课程名 VARCHAR(20))
CREATE TABLE 成绩表 (学生ID INT, 课程ID INT, 成绩 INT)

INSERT INTO 学生
SELECT 1,'张三' UNION ALL
SELECT 2,'李四' UNION ALL
SELECT 3,'王五'

INSERT INTO 课程
SELECT 1,'语文' UNION ALL
SELECT 2,'化学' UNION ALL
SELECT 3,'外语' UNION ALL
SELECT 4,'物理'

INSERT INTO 成绩表
SELECT 1,1,60 UNION ALL
SELECT 1,2,70 UNION ALL
SELECT 1,3,65 UNION ALL
SELECT 1,4,90 UNION ALL
SELECT 2,1,80 UNION ALL
SELECT 2,2,65 UNION ALL
SELECT 2,3,85 UNION ALL
SELECT 2,4,80 UNION ALL
SELECT 3,1,50 UNION ALL
SELECT 3,2,75 UNION ALL
SELECT 3,3,85 UNION ALL
SELECT 3,4,60

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

--方法二
SELECT 姓名,
MAX(CASE 课程名 WHEN '语文' THEN 成绩 ELSE 0 END) AS 语文,
MAX(CASE 课程名 WHEN '化学' THEN 成绩 ELSE 0 END) AS 化学,
MAX(CASE 课程名 WHEN '外语' THEN 成绩 ELSE 0 END) AS 外语,
MAX(CASE 课程名 WHEN '物理' THEN 成绩 ELSE 0 END) AS 物理
FROM (SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D 
INNER JOIN 学生 B ON B.学生ID=D.学生ID 
INNER JOIN 课程 C ON C.课程ID=D.课程ID) AS TMP GROUP BY 姓名


DROP TABLE 学生
DROP TABLE 课程
DROP TABLE 成绩表