mysql行转列转换

http://blog.csdn.net/sinat_27406925/article/details/77507478

mysql 行列转换，在项目中应用的极其频繁，尤其是一些金融项目里的报表。其中最为头痛的就是多行转多列，动态的列行转换。最近在研究这些行里转换，还是从最为简单的行列转换开始。

sql 脚本

-- 创建表  学生表

CREATE TABLE `student` (

    `stuid` VARCHAR() NOT NULL COMMENT '学号',

    `stunm` VARCHAR() NOT NULL COMMENT '学生姓名',

    PRIMARY KEY (`stuid`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB; 

-- 课程表 

CREATE TABLE `courses` (

    `courseno` VARCHAR() NOT NULL,

    `coursenm` VARCHAR() NOT NULL,

    PRIMARY KEY (`courseno`)

)

COMMENT='课程表'

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

-- 成绩表

CREATE TABLE `score` (

    `stuid` VARCHAR() NOT NULL,

    `courseno` VARCHAR() NOT NULL,

    `scores` FLOAT NULL DEFAULT NULL,

    PRIMARY KEY (`stuid`, `courseno`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

-- 插入数据

-- 学生表数据

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

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

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

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

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

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

-- 课程表数据

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('', 'C001', );

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--

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

要求: 查询每个学生的每门课程与每门成绩

 select   st.stuid ID ,  st.stunm 姓名, cs.coursenm 课程名 ,sc.scores 成绩     from  student st, score sc ,courses cs

where st.stuid = sc.stuid and sc.courseno = cs.courseno

结果:

这是4列27行

我们行转成列，ID对应姓名对应每门课程对应每门成绩

静态行专列

 select st.stuid 编号, st.stunm 姓名 ,

Max(case c.coursenm when '大学语文' then s.scores else  end ) '大学语文',

max(case c.coursenm when '新视野英语' then IFNULL(s.scores,)else  end) '新视野英语',

Max(case c.coursenm when '离散数学' then IFNULL(s.scores,) ELSE  END) '离散数学',

MAX(case c.coursenm when '概率论与数理统计' then IFNULL(s.scores,) else  end) '概率论与数理统计',

MAX(case c.coursenm  when '线性代数' then IFNULL(s.scores,) else  END) '线性代数',

MAX(case c.coursenm when '高等数学(一)' THEN IFNULL(s.scores,) else  end) '高等数学(一)',

MAX(case c.coursenm when '高等数学(二)' THEN IFNULL(s.scores,) else  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() 这个函数

group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。
比较抽象，难以理解。

通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函

数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

这些都是从网上看到的解释，但还是不好理解，我们直接上代码，看看run出来的结果，根据run之后的结果再回过来看！

select   s.stuid 编号 , GROUP_CONCAT(courseno) 课程号 , GROUP_CONCAT(s.scores)  成绩  from score s GROUP BY  s.stuid

看看运行后的结果:
之前效果:

非常明显GROUP_CONCAT() 作用 ,将课程号courseno, 成绩 scores 的结果集放在一起。

巴特西

mysql行转列转换

最新文章

热门文章