Mysql行转列的实现(静态+动态+统计)
·
应用场景:以学生为单位,查询出每个学生对应的每个科目的考试成绩。
创建示例数据库表
create table `scores` (
`id` int(11) not null auto_increment,
`user_id` int(11) default null comment '用户id',
`subject` varchar(100) default null comment '学科',
`score` decimal(5,2) default null comment '分数',
primary key (`id`)
) engine=innodb default charset=utf8;
插入示例数据
insert into `scores` values (1, 1, '哲学', 92);
insert into `scores` values (2, 1, '英语', 71);
insert into `scores` values (3, 1, '体育', 59);
insert into `scores` values (4, 1, '网络技术', 86);
insert into `scores` values (5, 1, '数据结构与算法', 93);
insert into `scores` values (6, 2, '哲学', 69);
insert into `scores` values (7, 2, '英语', 87);
insert into `scores` values (8, 2, '体育', 90);
insert into `scores` values (9, 2, '网络技术', 53);
insert into `scores` values (10, 2, '数据结构与算法', 51);
insert into `scores` values (11, 3, '哲学', 70);
insert into `scores` values (12, 3, '英语', 63);
insert into `scores` values (13, 3, '体育', 79);
insert into `scores` values (14, 3, '网络技术', 80);
insert into `scores` values (15, 3, '数据结构与算法', 81);
正常查询
select * from scores;
查询结果:

行转列查询,不含统计【静态方式】
select user_id,
max(case `subject` when '哲学' then score else 0 end) as '哲学',
max(case `subject` when '英语' then score else 0 end) as '英语',
max(case `subject` when '体育' then score else 0 end) as '体育',
max(case `subject` when '网络技术' then score else 0 end) as '网络技术',
max(case `subject` when '数据结构与算法' then score else 0 end) as '数据结构与算法'
from scores
group by user_id;
查询结果:

行转列查询,含统计【静态方式】
select user_id,
SUM(score) as '总分',
max(case `subject` when '哲学' then score else 0 end) as '哲学',
max(case `subject` when '英语' then score else 0 end) as '英语',
max(case `subject` when '体育' then score else 0 end) as '体育',
max(case `subject` when '网络技术' then score else 0 end) as '网络技术',
max(case `subject` when '数据结构与算法' then score else 0 end) as '数据结构与算法'
from scores
group by user_id;
查询结果:

行转列查询,不含统计【动态方式】
如果需要行转列的字段不确定,或者在字段比较多的情况下,推荐使用动态方式进行行转列。
SET @sql = NULL;
-- 拼接需要行转列的字段
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(subject = ''', subject, ''', score, 0)) as''', subject, '''' )) INTO @sql
FROM scores;
-- 预处理sql语句
SET @sql = CONCAT('select user_id, ', @sql, ' from scores group by user_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
查询结果:

行转列查询,含统计【动态方式】
SET @sql = NULL;
-- 拼接需要行转列的字段
SELECT CONCAT('SUM(score) as ''总分'',',GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(subject = ''', subject, ''', score, 0)) as ''', subject, '''' ))) INTO @sql
FROM scores;
-- 预处理sql语句
SET @sql = CONCAT('select user_id, ', @sql, ' from scores group by user_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
查询结果:

更多推荐

所有评论(0)