mysql行转列小技巧_mysql行转列小技巧

小鱼儿 2023-01-12 04:25 177阅读 0赞

参考文章http://blog.csdn.net/u013938484/article/details/50552747,测试了一下很不错。

下面是具体的测试过程：

数据表结构：

采用经典的学生，课程，成绩表：

CREATE TABLE \`student\` (

\`stuid\` VARCHAR(16) NOT NULL COMMENT '学号',\`stunm\` VARCHAR(20) NOT NULL COMMENT '学生姓名',PRIMARY KEY (\`stuid\`)

)

CREATE TABLE \`courses\` (

\`courseno\` VARCHAR(20) NOT NULL,\`coursenm\` VARCHAR(100) NOT NULL,PRIMARY KEY (\`courseno\`)

)

COMMENT='课程表'CREATE TABLE \`score\` (

\`stuid\` VARCHAR(16) NOT NULL,\`courseno\` VARCHAR(20) NOT NULL,\`scores\` FLOAT NULL DEFAULT NULL,PRIMARY KEY (\`stuid\`,\`courseno\`)

) 数据准备：

/\*学生表数据\*/

Insert Into student (stuid,stunm) Values('1001','张三');

Insert Into student (stuid,stunm) Values('1002','李四');

Insert Into student (stuid,stunm) Values('1003','赵二');

Insert Into student (stuid,stunm) Values('1004','王五');

Insert Into student (stuid,stunm) Values('1005','刘青');

Insert Into student (stuid,stunm) Values('1006','周明');

/\*课程表数据\*/

Insert Into courses (courseno,coursenm) Values('C001','大学语文');

Insert Into courses (courseno,coursenm) Values('C002','新视野英语');

Insert Into courses (courseno,coursenm) Values('C003','离散数学');

Insert Into courses (courseno,coursenm) Values('C004','概率论与数理统计');

Insert Into courses (courseno,coursenm) Values('C005','线性代数');

Insert Into courses (courseno,coursenm) Values('C006','高等数学(一)');

Insert Into courses (courseno,coursenm) Values('C007','高等数学(二)');

/\*成绩表数据\*/

Insert Into score(stuid,courseno,scores) Values('1001','C001',67);

Insert Into score(stuid,courseno,scores) Values('1002','C001',68);

Insert Into score(stuid,courseno,scores) Values('1003','C001',69);

Insert Into score(stuid,courseno,scores) Values('1004','C001',70);

Insert Into score(stuid,courseno,scores) Values('1005','C001',71);

Insert Into score(stuid,courseno,scores) Values('1006','C001',72);

Insert Into score(stuid,courseno,scores) Values('1001','C002',87);

Insert Into score(stuid,courseno,scores) Values('1002','C002',88);

Insert Into score(stuid,courseno,scores) Values('1003','C002',89);

Insert Into score(stuid,courseno,scores) Values('1004','C002',90);

Insert Into score(stuid,courseno,scores) Values('1005','C002',91);

Insert Into score(stuid,courseno,scores) Values('1006','C002',92);

Insert Into score(stuid,courseno,scores) Values('1001','C003',83);

Insert Into score(stuid,courseno,scores) Values('1002','C003',84);

Insert Into score(stuid,courseno,scores) Values('1003','C003',85);

Insert Into score(stuid,courseno,scores) Values('1004','C003',86);

Insert Into score(stuid,courseno,scores) Values('1005','C003',87);

Insert Into score(stuid,courseno,scores) Values('1006','C003',88);

Insert Into score(stuid,courseno,scores) Values('1001','C004',88);

Insert Into score(stuid,courseno,scores) Values('1002','C004',89);

Insert Into score(stuid,courseno,scores) Values('1003','C004',90);

Insert Into score(stuid,courseno,scores) Values('1004','C004',91);

Insert Into score(stuid,courseno,scores) Values('1005','C004',92);

Insert Into score(stuid,courseno,scores) Values('1006','C004',93);

Insert Into score(stuid,courseno,scores) Values('1001','C005',77);

Insert Into score(stuid,courseno,scores) Values('1002','C005',78);

Insert Into score(stuid,courseno,scores) Values('1003','C005',79);

Insert Into score(stuid,courseno,scores) Values('1004','C005',80);

Insert Into score(stuid,courseno,scores) Values('1005','C005',81);

Insert Into score(stuid,courseno,scores) Values('1006','C005',82);

Insert Into score(stuid,courseno,scores) Values('1001','C006',77);

Insert Into score(stuid,courseno,scores) Values('1002','C006',78);

Insert Into score(stuid,courseno,scores) Values('1003','C006',79);

Insert Into score(stuid,courseno,scores) Values('1004','C006',80);

Insert Into score(stuid,courseno,scores) Values('1005','C006',81);

Insert Into score(stuid,courseno,scores) Values('1006','C006',82);

方法一：

静态的行转列：缺点是要知道固定的列，方可使用：

Select st.stuid,st.stunm,MAX(CASE c.coursenm WHEN '大学语文'THEN s.scores ELSE 0END ) '大学语文',MAX(CASE c.coursenm WHEN '新视野英语'THEN ifnull(s.scores,0) ELSE 0END ) '新视野英语',MAX(CASE c.coursenm WHEN '离散数学'THEN ifnull(s.scores,0) ELSE 0END ) '离散数学',MAX(CASE c.coursenm WHEN '概率论与数理统计'THEN ifnull(s.scores,0) ELSE 0END ) '概率论与数理统计',MAX(CASE c.coursenm WHEN '线性代数'THEN ifnull(s.scores,0) ELSE 0END ) '线性代数',MAX(CASE c.coursenm WHEN '高等数学(一)'THEN ifnull(s.scores,0) ELSE 0END ) '高等数学(一)',MAX(CASE c.coursenm WHEN '高等数学(二)'THEN ifnull(s.scores,0) ELSE 0END ) '高等数学(二)'From student st

Left Join score s On st.stuid = s.stuid

Left Join courses c On c.courseno = s.courseno

Group by st.stuid 执行结果如下：

![0818b9ca8b590ca3270a3433284dd417.png][]

方法二：动态的行转列

在这里要借住存储过程，代码段如下：

DELIMITER &&

drop procedure ifexists SP\_QueryData;

Create Procedure SP\_QueryData(IN stuid varchar(16))

READS SQL DATA

BEGIN

SET @sql = NULL;

SET @stuid = NULL;

SELECT

GROUP\_CONCAT(DISTINCT

CONCAT(

'MAX(IF(c.coursenm = ''',c.coursenm,''', s.scores, 0)) AS ''',c.coursenm,'\\'')

) INTO @sql

FROM courses c;

SET @sql = CONCAT('Select st.stuid, st.stunm, ',@sql,'From student stLeft Join score s On st.stuid = s.stuid

Left Join courses c On c.courseno = s.courseno');IF stuid is notnull andstuid <> ''then

SET @stuid = stuid;

SET @sql = CONCAT(@sql,' Where st.stuid =\\'',@stuid,'\\'');

END IF;

SET @sql = CONCAT(@sql,' Group by st.stuid');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END &&

DELIMITER ;

call \`SP\_QueryData\`();

mysql> DELIMITER &&

mysql> drop procedure ifexists SP\_QueryData;

\-> Create Procedure SP\_QueryData(IN stuid varchar(16))

\-> READS SQL DATA

\-> BEGIN

\->

\-> SET @sql = NULL;

\-> SET @stuid = NULL;

\-> SELECT

\-> GROUP\_CONCAT(DISTINCT

\-> CONCAT(

\-> 'MAX(IF(c.coursenm = ''',-> c.coursenm,-> ''', s.scores, 0)) AS ''',-> c.coursenm,'\\''-> )

\-> ) INTO @sql

\-> FROM courses c;

\->

\-> SET @sql = CONCAT('Select st.stuid, st.stunm, ',@sql,-> 'From student st'> Left Join score s On st.stuid = s.stuid'> Left Join courses c On c.courseno = s.courseno');

\->

\-> IF stuid is notnull andstuid <> ''then

\-> SET @stuid = stuid;

\-> SET @sql = CONCAT(@sql,' Where st.stuid =\\'',@stuid,'\\'');

\-> END IF;

\->

\-> SET @sql = CONCAT(@sql,' Group by st.stuid');

\->

\-> PREPARE stmt FROM @sql;

\-> EXECUTE stmt;

\-> DEALLOCATE PREPARE stmt;

\->

\-> END &&

Query OK,0rows affected,1warning (0.00sec)

Query OK,0rows affected (0.00sec)

执行结果：

![0818b9ca8b590ca3270a3433284dd417.png][]

分部解释一下：

1，首先要获得动态的组合语句：

SELECTGROUP\_CONCAT(DISTINCTCONCAT('MAX(IF(c.coursenm = ''',c.coursenm,''', s.scores, 0)) AS ''',c.coursenm,''''))FROM courses c; 执行结果：

![0818b9ca8b590ca3270a3433284dd417.png][]

2，因为是拼接语句，后面即时加上where语句，得到的结果也不是我们想要的，正在这里要像普通的语句那样，进行声明，将语句拼接完整之后，在执行：

代码：

SET @sql = NULL;

SELECT

GROUP\_CONCAT(DISTINCT

CONCAT(

'MAX(IF(c.coursenm = ''',c.coursenm,''', s.scores, 0)) AS ''',c.coursenm,'''')) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,' From student stLeft Join score s On st.stuid = s.stuidLeft Join courses c On c.courseno = s.coursenoGroup by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;

执行结果：

![0818b9ca8b590ca3270a3433284dd417.png][]

这样在sql中执行，可得到结果。

注意几个地方：

1. if条件的使用，就是直接用，不需要括号。

详细内容请参考原文，写的比较详细，我这里抽取骨干，拿来直接测试一下。

[0818b9ca8b590ca3270a3433284dd417.png]: /images/20221119/e75d8d83f415461b82837e0d9f59b11e.png