经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
MySql行转列、列转行
来源:cnblogs  作者:zsq_fengchen  时间:2018/12/11 9:19:04  对本文有异议

现mysql中有一张表php_user表,表结构为: 

表中数据有: 

现在想查询出来不同学生的语数外成绩在一行显示,那么需要用到行转列的用法,

一、行转列

1、使用case...when....then 进行行转列
MAX(case when 条件 then 列内容 else 不匹配时显示内容 end) 列名。具体sql如下:

2、使用IF() 进行行转列:

3、利用SUM(IF()) 生成列,直接生成结果不再利用子查询

二、列转行

 建表语句:

复制代码
CREATE TABLE tb_score1(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    cn_score DOUBLE COMMENT '语文成绩',
    math_score DOUBLE COMMENT '数学成绩',
    en_score DOUBLE COMMENT '英语成绩',
    po_score DOUBLE COMMENT '政治成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
复制代码

 插入数据:

INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);

 查询数据表中的内容(即转换前的结果)

SELECT * FROM tb_score1

转换后:

本质是将userid的每个科目分数分散成一条记录显示出来。

直接上SQL:

复制代码
SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid
复制代码

这里将每个userid对应的多个科目的成绩查出来,通过UNION ALL将结果集加起来,达到上图的效果。

附:UNION与UNION ALL的区别(摘):

   1.对重复结果的处理:UNION会去掉重复记录,UNION ALL不会;

   2.对排序的处理:UNION会排序,UNION ALL只是简单地将两个结果集合并;

   3.效率方面的区别:因为UNION 会做去重和排序处理,因此效率比UNION ALL慢很多;

 

 友情链接: NPS  问卷模板