MySQL 实现行转列SQL

落日映苍穹つ 2022-05-23 06:09 220阅读 0赞

# 概述 #

好久没写SQL语句，今天看到问答中的一个问题，拿来研究一下。

问题链接：[关于Mysql 的分级输出问题][Mysql]

# 情景简介 #

学校里面记录成绩，每个人的选课不一样,而且以后会添加课程，所以不需要把所有课程当作列。数据表里面数据如下图，使用姓名+课程作为联合主键（有些需求可能不需要联合主键）。本文以[MySQL][]为基础，其他[数据库][MySQL]会有些许语法不同。

数据库表数据：

![Center][]![Center 1][]

# 处理后的结果（行转列）： #

![Center 2][] ![Center 3][]

方法一：

这里可以使用Max，也可以使用Sum；

注意第二张图，当有学生的某科成绩缺失的时候，输出结果为Null;

\[sql\]  [view plain][]   [copy][view plain]

1.  **SELECT**  
2.      SNAME,  
3.      **MAX**(  
4.          CASE CNAME  
5.          **WHEN** 'JAVA' **THEN**  
6.              SCORE  
7.          **END**  
8.      ) JAVA,  
9.      **MAX**(  
10.         CASE CNAME  
11.         **WHEN** 'mysql' **THEN**  
12.             SCORE  
13.         **END**  
14.     ) mysql  
15. **FROM**  
16.     stdscore  
17. **GROUP** **BY**  
18.     SNAME;

可以在第一个Case中加入Else语句解决这个问题:

\[sql\]  [view plain][]   [copy][view plain]

1.  **SELECT**  
2.      SNAME,  
3.      **MAX**(  
4.          CASE CNAME  
5.          **WHEN** 'JAVA' **THEN**  
6.              SCORE  
7.          **ELSE**  
8.              0  
9.          **END**  
10.     ) JAVA,  
11.     **MAX**(  
12.         CASE CNAME  
13.         **WHEN** 'mysql' **THEN**  
14.             SCORE  
15.         **ELSE**  
16.             0  
17.         **END**  
18.     ) mysql  
19. **FROM**  
20.     stdscore  
21. **GROUP** **BY**  
22.     SNAME;

方法二：

\[sql\]  [view plain][]   [copy][view plain]

1.  **SELECT** **DISTINCT**  a.sname,  
2.  (**SELECT** score **FROM** stdscore b **WHERE** a.sname=b.sname AND b.CNAME='JAVA' ) **AS** 'JAVA',  
3.  (**SELECT** score **FROM** stdscore b **WHERE** a.sname=b.sname AND b.CNAME='mysql' ) **AS** 'mysql'  
4.  **FROM** stdscore a

方法三：

\[sql\]  [view plain][]   [copy][view plain]

1.  **DROP** **PROCEDURE**  
2.  IF EXISTS sp\_score;  
3.  DELIMITER &&  
4.    
5.  **CREATE** **PROCEDURE** sp\_score ()  
6.  **BEGIN**  
7.      \#课程名称  
8.      **DECLARE**  
9.          cname\_n **VARCHAR** (20) ; \#所有课程数量  
10.         **DECLARE**  
11.             count **INT** ; \#计数器  
12.             **DECLARE**  
13.                 i **INT** **DEFAULT** 0 ; \#拼接SQL字符串  
14.             **SET** @s = 'SELECT sname' ;  
15.             **SET** count = (  
16.                 **SELECT**  
17.                     COUNT(**DISTINCT** cname)  
18.                 **FROM**  
19.                     stdscore  
20.             ) ;  
21.             WHILE i < count DO  
22.   
23.   
24.             **SET** cname\_n = (  
25.                 **SELECT**  
26.                     cname  
27.                 **FROM**  
28.                     stdscore  
29.                 **GROUP** **BY** CNAME   
30.                 LIMIT i,  
31.                 1  
32.             ) ;  
33.             **SET** @s = CONCAT(  
34.                 @s,  
35.                 ', SUM(CASE cname WHEN ',  
36.                 '\\'',  
37.                 cname\_n,  
38.                 '\\'',  
39.                 ' THEN score ELSE 0 END)',  
40.                 ' AS ',  
41.                 '\\'',  
42.                 cname\_n,  
43.                 '\\''  
44.             ) ;  
45.             **SET** i = i + 1 ;  
46.             **END**  
47.             WHILE ;  
48.             **SET** @s = CONCAT(  
49.                 @s,  
50.                 ' FROM stdscore GROUP BY sname'  
51.             ) ; \#用于调试  
52.             \#**SELECT** @s;  
53.             **PREPARE** stmt  
54.             **FROM**  
55.                 @s ; **EXECUTE** stmt ;  
56.             **END**&&  
57.   
58. CALL sp\_score () ;

# 处理后的结果（行转列）分级输出： #

![Center 4][] ![Center 5][]

方法一：

这里可以使用Max，也可以使用Sum；

注意第二张图，当有学生的某科成绩缺失的时候，输出结果为Null;

\[sql\]  [view plain][]   [copy][view plain]

1.  **SELECT**  
2.      SNAME,  
3.      **MAX**(  
4.          CASE CNAME  
5.          **WHEN** 'JAVA' **THEN**  
6.              (  
7.                  CASE  
8.                  **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 20 **THEN**  
9.                      '优秀'  
10.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 10 **THEN**  
11.                     '良好'  
12.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') >= 0 **THEN**  
13.                     '普通'  
14.                 **ELSE**  
15.                     '较差'  
16.                 **END**  
17.             )  
18.         **END**  
19.     ) JAVA,  
20.     **MAX**(  
21.         CASE CNAME  
22.         **WHEN** 'mysql' **THEN**  
23.             (  
24.                 CASE  
25.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 20 **THEN**  
26.                     '优秀'  
27.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 10 **THEN**  
28.                     '良好'  
29.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') >= 0 **THEN**  
30.                     '普通'  
31.                 **ELSE**  
32.                     '较差'  
33.                 **END**  
34.             )  
35.         **END**  
36.     ) mysql  
37. **FROM**  
38.     stdscore  
39. **GROUP** **BY**  
40.     SNAME;

方法二：

\[sql\]  [view plain][]   [copy][view plain]

1.  **SELECT** **DISTINCT**  a.sname,  
2.  (**SELECT** (  
3.                  CASE  
4.                  **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 20 **THEN**  
5.                      '优秀'  
6.                  **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 10 **THEN**  
7.                      '良好'  
8.                  **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') >= 0 **THEN**  
9.                      '普通'  
10.                 **ELSE**  
11.                     '较差'  
12.                 **END**  
13.             ) **FROM** stdscore b **WHERE** a.sname=b.sname AND b.CNAME='JAVA' ) **AS** 'JAVA',  
14. (**SELECT** (  
15.                 CASE  
16.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 20 **THEN**  
17.                     '优秀'  
18.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') > 10 **THEN**  
19.                     '良好'  
20.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME='JAVA') >= 0 **THEN**  
21.                     '普通'  
22.                 **ELSE**  
23.                     '较差'  
24.                 **END**  
25.             ) **FROM** stdscore b **WHERE** a.sname=b.sname AND b.CNAME='mysql' ) **AS** 'mysql'  
26. **FROM** stdscore a

方法三：

\[sql\]  [view plain][]   [copy][view plain]

1.  **DROP** **PROCEDURE**  
2.  IF EXISTS sp\_score;  
3.  DELIMITER &&  
4.    
5.  **CREATE** **PROCEDURE** sp\_score ()  
6.  **BEGIN**  
7.      \#课程名称  
8.      **DECLARE**  
9.          cname\_n **VARCHAR** (20) ; \#所有课程数量  
10.         **DECLARE**  
11.             count **INT** ; \#计数器  
12.             **DECLARE**  
13.                 i **INT** **DEFAULT** 0 ; \#拼接SQL字符串  
14.             **SET** @s = 'SELECT sname' ;  
15.             **SET** count = (  
16.                 **SELECT**  
17.                     COUNT(**DISTINCT** cname)  
18.                 **FROM**  
19.                     stdscore  
20.             ) ;  
21.             WHILE i < count DO  
22.   
23.   
24.             **SET** cname\_n = (  
25.                 **SELECT**  
26.                     cname  
27.                 **FROM**  
28.                     stdscore  
29.         **GROUP** **BY** CNAME   
30.                 LIMIT i, 1  
31.             ) ;  
32.             **SET** @s = CONCAT(  
33.                 @s,  
34.                 ', MAX(CASE cname WHEN ',  
35.                 '\\'',  
36.                 cname\_n,  
37.                 '\\'',  
38.                 ' **THEN** (  
39.                 CASE  
40.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME=\\'',cname\_n,'\\') > 20 **THEN**  
41.                     \\'优秀\\'  
42.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME=\\'',cname\_n,'\\') > 10 **THEN**  
43.                     \\'良好\\'  
44.                 **WHEN** SCORE - (**select** avg(SCORE) **from** stdscore **where** CNAME=\\'',cname\_n,'\\') >= 0 **THEN**  
45.                     \\'普通\\'  
46.                 **ELSE**  
47.                     \\'较差\\'  
48.                 **END**  
49.             ) **END**)',  
50.                 ' AS ',  
51.                 '\\'',  
52.                 cname\_n,  
53.                 '\\''  
54.             ) ;  
55.             **SET** i = i + 1 ;  
56.             **END**  
57.             WHILE ;  
58.             **SET** @s = CONCAT(  
59.                 @s,  
60.                 ' FROM stdscore GROUP BY sname'  
61.             ) ;   
62.             \#用于调试  
63.             \#**SELECT** @s;  
64.             **PREPARE** stmt  
65.             **FROM**  
66.                 @s ; **EXECUTE** stmt ;  
67.             **END**&&  
68.   
69.   
70. CALL sp\_score ();

# 几种方法比较分析 #

第一种使用了分组，对每个课程分别处理。

第二种方法使用了表连接。  
第三种使用了存储过程，实际上可以是第一种或第二种方法的动态化，先计算出所有课程的数量，然后对每个分组进行课程查询。 这种方法的一个最大的好处是当新增了一门课程时，SQL语句不需要重写。

# 小结 #

关于行转列和列转行

这个概念似乎容易弄混，有人把行转列理解为列转行，有人把列转行理解为行转列；

这里做个定义：

行转列：把表中特定列（如本文中的：CNAME）的数据去重后做为列名（如查询结果行中的“[Java][]，mysql”，处理后是做为列名输出）；

列转行：可以说是行转列的反转，把表中特定列（如本文处理结果中的列名“JAVA，mysql”）做为每一行数据对应列“CNAME”的值；

关于效率

不知道有什么好的生成模拟数据的方法或工具，麻烦小伙伴推荐一下，抽空我做一下对比；

还有其它更好的方法吗？

本文使用的几种方法应该都有优化的空间，特别是使用存储过程的话会更加灵活，功能更强大；

本文的分级只是给出一种思路，分级的方法如果学生的成绩相差较小的话将失去意义；

如果小伙伴有更好的方法，还请不吝赐教，感激不尽！

有些需求可能不需要联合主键

有些需求可能不需要联合主键，因为一门课程可能允许学生考多次，取最好的一次成绩，或者取多次的平均成绩。

原文地址： [http://blog.csdn.net/testcs\_dn/article/details/49847299][http_blog.csdn.net_testcs_dn_article_details_49847299]

[Mysql]: http://ask.csdn.net/questions/219635
[MySQL]: http://lib.csdn.net/base/mysql
[Center]: https://img-blog.csdn.net/20151115101718151?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: /images/20220523/28600503aa3d42729705d5c4cbbe6386.png
[Center 2]: https://img-blog.csdn.net/20151115101804700?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: /images/20220523/45ec3aa1de004ec288a79091b85cd5e2.png
[view plain]: https://blog.csdn.net/sxdtzhaoxinguo/article/details/55519171#
[Center 4]: https://img-blog.csdn.net/20151115102000356?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 5]: /images/20220523/266f6598a99d49349ca24188b7875030.png
[Java]: http://lib.csdn.net/base/javase
[http_blog.csdn.net_testcs_dn_article_details_49847299]: http://blog.csdn.net/testcs_dn/article/details/49847299