MySQL行转列问题及内部排序问题

常规情况下是用不到行转列的,但是在今天做Echarts图标的时候,发现查出来的数据是纵向的,所以查了下行转列及GROUP_CONCAT

常规行转列

  1. 数据脚本
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    -- 创建表  学生表
    CREATE TABLE `student` (
    `stuid` VARCHAR(16) NOT NULL COMMENT '学号',
    `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',
    PRIMARY KEY (`stuid`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;


    -- 课程表

    CREATE TABLE `courses` (
    `courseno` VARCHAR(20) NOT NULL,
    `coursenm` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`courseno`)
    )
    COMMENT='课程表'
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;


    -- 成绩表
    CREATE TABLE `score` (
    `stuid` VARCHAR(16) NOT NULL,
    `courseno` VARCHAR(20) NOT NULL,
    `scores` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`stuid`, `courseno`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;

    -- 插入数据

    -- 学生表数据

    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);
    --
    select st.stuid,st.stunm from student st;
    select sc.stuid , sc.courseno,sc.scores from score sc ;
    select cs.courseno,cs.coursenm from courses cs;

  2. 要求查询每个学生的 每门课程与每门成绩
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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

GROUP_CONCAT

该函数返回带有来自一个组的连接的非NULL值的字符串结果。白话解释:group_concat()会计算哪些行属于同一组,将属于同一组的列显示出来。要返回哪些列,由函数参数(就是字段名)决定。分组必须有个标准,就是根据group by指定的列进行分组

1
2
3
4
5
6
7
8
SELECT
s.stuid 编号,
GROUP_CONCAT(courseno) 课程号,
GROUP_CONCAT(s.scores) 成绩
FROM
score s
GROUP BY
s.stuid

查询结果图

GROUP_CONCAT内部排序

不能直接查询出来就算了,在Echarts里面需要进行日期的排序,因此就有了如下SQL,也就是出来的数据能够自定义进行排序

1
GROUP_CONCAT( columnA ORDER BY columnA)

本文引自mysql行转列转换