Oracle和Mysql和mssql中将多行记录合并为一行
来源:互联网 发布:audacity mac下载 编辑:程序博客网 时间:2024/06/05 06:53
测试表与测试数据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 成绩表
PS:用嵌套SELECT与用聚合函数加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?对,在将行转换为列时,也许这种方法是最优先考虑到(或者你是高手,所以不是优先考虑到这个而是其它),所以我最开始也写出了下面这条语句:
(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语句如下:
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, 课程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 成绩表
PS:用嵌套SELECT与用聚合函数加Case两者的效率如何,我没有测试,各位有兴趣的可测试一下
- Oracle和Mysql和mssql中将多行记录合并为一行
- ORACLE多行合并为一行
- MySQL多行结果合并为一行
- SQL合并多行记录为一行(网抄文章)
- Oracle多行记录合并成一行展示
- 记录oracle 多行合并成一行(备忘)
- oracle 中将 多行值合并成一行
- SQL/Oracle 将一列多行合并为一行
- ORACLE多行合并为一行(row_number,sys_connect_by_path)
- 多行合并为一行
- mysql数据库中怎样实现多行合并为一行?
- mysql多行数据合并为一行
- oracle多行合并一行
- mysql,oracle合并单列多行记录
- 用sql实现两行记录合并为一行记录
- MSSQL2005中将多行合并为一行的方法,此方法不再使用sql2000中函数转换的办法
- Oracle中将一条记录(某字段中为合并的信息)拆分成多条记录
- sql server中将一列多行的数据转成一列一行,和一行数据解析成一行多列
- zoj1507 Crazy Search (hash )
- sizeof()用法汇总
- Java 线程操作的相关方法
- JavaScript中绑定事件监听函数的通用方法[ addEvent() ]
- boost编译过程
- Oracle和Mysql和mssql中将多行记录合并为一行
- 使用showModalDialog打开模态窗口添加数据后刷新原窗口 .
- 2013网易技术类笔试题(java开发方向+移动平台开发)
- Linux下Matlab安装
- 计算机科学与技术专业的困惑
- 并行计算基础及工具
- 外部程序启动器MFC
- Linux下管理java进程
- 有关Request对象 10.10