经典SQL问题: 行转列,列转行
情景简介
学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据库grade里面数据如下图,假定每个人姓名都不一样,作为主键。本文以MySQL为基础,其他数据库会有些许语法不同。
数据库数据:
mysql> select * from grade; +------+--------+--------+-------+ | id | name | course | score | +------+--------+--------+-------+ | 1 | 张三 | 语文 | 80 | | 2 | 张三 | 数学 | 90 | | 3 | 张三 | 英语 | 90 | | 4 | 李四 | 语文 | 47 | | 5 | 李四 | 数学 | 78 | | 6 | 王五 | 数学 | 97 | +------+--------+--------+-------+ 6 rows in set (0.00 sec) mysql>
处理后效果:
+--------+--------+--------+--------+ | name | 语文 | 数学 | 英语 | +--------+--------+--------+--------+ | 张三 | 80 | 90 | 90 | | 李四 | 47 | 78 | NULL | | 王五 | NULL | 97 | NULL | +--------+--------+--------+--------+ 3 rows in set (0.00 sec) mysql>
下面介绍三种方法:
方法一:
SELECT DISTINCT a.name, (SELECT score FROM grade b WHERE a.name=b.name AND b.course='语文' ) AS '语文', (SELECT score FROM grade b WHERE a.name=b.name AND b.course='数学' ) AS '数学', (SELECT score FROM grade b WHERE a.name=b.name AND b.course='英语' ) AS '英语' FROM grade a
方法二:
SELECT name, SUM(CASE course WHEN '语文' THEN score END ) AS '语文', SUM(CASE course WHEN '数学' THEN score END ) AS '数学', SUM(CASE course WHEN '英语' THEN score END ) AS '英语' FROM grade GROUP BY name
方法三:
DELIMITER && CREATE PROCEDURE sp_count() BEGIN #课程名称 DECLARE course_n VARCHAR(20); #所有课程数量 DECLARE count INT; #计数器 DECLARE i INT DEFAULT 0; #拼接SQL字符串 SET @s = 'SELECT name'; SET count = (SELECT COUNT(distinct course) FROM grade); WHILE i < count DO SET course_n = (SELECT course FROM grade LIMIT i,1); SET @s = CONCAT(@s, ', SUM(CASE course WHEN ','\'', course_n,'\'',' THEN score END )',' AS ','\'',course_n,'\''); SET i = i+1; END WHILE; SET @s = CONCAT(@s, ' FROM grade GROUP BY name'); #用于调试 #SELECT @s; PREPARE stmt FROM @s; EXECUTE stmt; END && call sp_count();
方法分析:
第一种方法使用了表连接。
第二种使用了分组,对每个分组分别处理。
第三种使用了存储过程,其实是第二种方法的动态化,先计算出所有课程的数量,然后对每个分组进行课程查询。
很明显前两种方法属于硬编码,增加课程后就需要修改SQL。而第三种则没有这种问题。
Note:
MySQL中不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
本来想在方法三里面写上:DROP PROCEDURE IF EXISTS sp_count();这是错误的。调试的时候如果写错了,只能手动删除了,也没找到好方法。
结果转出行或者一个拼接字符串:《mysql函数之五:group_concat mysql 把结果集中的一列数据用指定分隔符转换成一行》
二、列转行
原来是这样
名称 单价 进货价
内存 120 100
现在想这样
名称 价格
内存 120
内存 100
mysql> select * from device_info; +--------+--------+-----------+ | dname | danjia | jinhuojia | +--------+--------+-----------+ | 内存 | 100 | 80 | | CPU | 200 | 150 | | 硬盘 | 300 | 230 | +--------+--------+-----------+ 3 rows in set (0.00 sec) mysql>
sql:
mysql> SELECT * FROM -> (SELECT dname, danjia AS jiage FROM device_info -> UNION ALL -> SELECT dname,jinhuojia AS jiage FROM device_info) AS temp -> ORDER BY dname; +--------+-------+ | dname | jiage | +--------+-------+ | CPU | 150 | | CPU | 200 | | 内存 | 80 | | 内存 | 100 | | 硬盘 | 230 | | 硬盘 | 300 | +--------+-------+ 6 rows in set (0.03 sec) mysql>