漫学笔记之mysql 行转列

妙手空空 2017-02-28 14:29

以下是测试数据

CREATE TABLE student (

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

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

PRIMARY KEY (stuid)

);

Insert Into student Values

('1001', '西施'),

('1002', '王昭君'),

('1003', '貂蝉');

CREATE TABLE courses (

courseid VARCHAR(20) NOT NULL comment '课程编号',

coursenm VARCHAR(100) NOT NULL comment '课程名称',

PRIMARY KEY (courseid)

)COMMENT='课程表';

insert into courses VALUES

('c1001','琴'),

('c1002','棋'),

('c1003','书'),

('c1004','画');

CREATE TABLE score (

scid varchar(16) not NULL comment'成绩编号',

stuid VARCHAR(16) NOT NULL comment'学生编号',

courseid VARCHAR(20) NOT NULL comment'课程编号',

scores FLOAT NULL DEFAULT NULL comment'成绩',

PRIMARY KEY (scid)

);

insert into score values

('1','1001','c1001',80),

('2','1001','c1002',90),

('3','1001','c1003',82),

('4','1001','c1004',83),

('5','1002','c1001',83),

('6','1002','c1002',90),

('7','1002','c1003',85),

('8','1002','c1004',86),

('9','1003','c1001',90),

('10','1003','c1002',89),

('11','1003','c1003',82),

('12','1003','c1004',88);

select score.stuid ,stunm , coursenm,scores from score

inner join student on student.stuid = score.stuid

inner join courses on courses.courseid = score.courseid order by stuid , scores ;

查询结果：

行转列 1 使用max函数

select score.stuid,stunm,

max( case WHEN courses.coursenm = '琴' then ifnull(score.scores,0) end ) '琴' ,

max( case WHEN courses.coursenm = '棋' then ifnull(score.scores,0) end ) '棋' ,

max( case WHEN courses.coursenm = '书' then ifnull(score.scores,0) end ) '书' ,

max( case WHEN courses.coursenm = '画' then ifnull(score.scores,0) end ) '画'

from score

inner join courses on courses.courseid = score.courseid

inner join student on student.stuid = score.stuid

group by score.stuid;

结果：

行转列 2 使用sum函数

select score.courseid 课程编号,courses.coursenm 课程名称,

sum( case WHEN student.stunm = '西施' then ifnull(score.scores,0) end ) '西施' ,

sum( case WHEN student.stunm = '王昭君' then ifnull(score.scores,0) end ) '王昭君' ,

sum( case WHEN student.stunm = '貂蝉' then ifnull(score.scores,0) end ) '貂蝉'

from score

inner join courses on courses.courseid = score.courseid

inner join student on student.stuid = score.stuid

group by score.courseid;

结果：

列传行 新建表并 插入数据

create table result (

stuid varchar(16) not null comment'学生编号',

stunm varchar(20) not null comment'学生姓名',

qin FLOAT NULL DEFAULT NULL comment'琴成绩',

qi FLOAT NULL DEFAULT NULL comment'棋成绩',

shu FLOAT NULL DEFAULT NULL comment'书成绩',

hua FLOAT NULL DEFAULT NULL comment'画成绩'

);

insert into result VALUES

('1001','西施',80,90,82,83),

('1002','王昭君',83,90,85,86),

('1003','貂蝉',90,90,82,88);

select * from result

查询结果：

列转行 union

UNION 用于合并两个或多个 SELECT 语句的结果集。

select stuid , stunm , '琴' 课程 , qin as 成绩 from result

union

select stuid , stunm , '棋' 课程 , qi as 成绩 from result

union

select stuid , stunm , '书' 课程 , shu as 成绩 from result

union

select stuid , stunm , '画' 课程 , hua as 成绩 from result

order by stuid , 课程

查询结果：