mysql行转列小技巧

标签：Insert 技巧 courseno Into scores 转列 Values mysql stuid

下面是具体的测试过程：

数据表结构：

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

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 0 END ) '大学语文',
    MAX(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语', 
    MAX(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学',
    MAX(CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull(s.scores,0) ELSE 0 END ) '概率论与数理统计',
    MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',
    MAX(CASE c.coursenm WHEN '高等数学(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(一)',
    MAX(CASE c.coursenm WHEN '高等数学(二)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(二)'
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

执行结果如下：

mysql行转列小技巧_sql

方法二：动态的行转列

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

DELIMITER &&
drop procedure if exists 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 not null and stuid <> '' 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 if exists 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 not null and stuid <> '' 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, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

执行结果：

mysql行转列小技巧_离散数学_02

分部解释一下：

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

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  )
FROM courses c;

执行结果：

mysql行转列小技巧_sql_03

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

代码：

SET @sql = 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
                        Group by st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;

执行结果：

mysql行转列小技巧_sql_04

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

注意几个地方：

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

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

标签：Insert,技巧,courseno,Into,scores,转列,Values,mysql,stuid
From： https://blog.51cto.com/u_16152230/6427032

相关文章

赞助商

阅读排行