SQL语句实现行转列
今天突然听到说如何行转列,就去了解了一下。
行转列:顾名思义就是我们用查询的sql语句查出来的数据将需要的行转成列。
就比如我们现在在数据库的一张学生成绩表是这样的
需求:我们想变成姓名-对应的科目-分数,这样来显示,就需要用到行转列
实现行转列我们先学习CASE WHEN的用法
case when的使用我们可以参照这里链接(转载另一个博主的,不做商用): https://blog.csdn.net/muriyue6/article/details/73442054
下面我们开始行转列吧
- 首先建表和插入相应的数据
CREATE TABLE `studentscores` (
`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`subject` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`score` float NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- ----------------------------
插入:
INSERT INTO `studentscores` VALUES ('二麻子', '语文', 95);
INSERT INTO `studentscores` VALUES ('三德子', '英语', 80);
INSERT INTO `studentscores` VALUES ('二麻子', '数学', 80);
INSERT INTO `studentscores` VALUES ('三德子', '语文', 90);
INSERT INTO `studentscores` VALUES ('二麻子', '英语', 75);
INSERT INTO `studentscores` VALUES ('三德子', '数学', 90);
- 写SQL语句完成行转列
SELECT t.username,
max(CASE t.subject WHEN '语文' THEN t.score ELSE 0 END)as '语文',
max(CASE t.subject WHEN '数学' THEN t.score ELSE 0 END)as '数学',
max(CASE t.subject WHEN '英语' THEN t.score ELSE 0 END)as '英语'
FROM studentscores t
GROUP BY t.username
这样我们就得到了我们想要的格式
除了这个我还写了一个例子,做一个统计的行转列
老规矩先看需求和两组对比
统计几个服务部的一些数据
数据库的数据是这样现实的
我们需要做相关的统计,将这些数据作为表头来统计,如下效果
下面开始使用SQL转换
- 首先建表插入相应数据
建表:
CREATE TABLE `testcounthl` (
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`code` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- -----------------------------------------------------
插入:
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'dcl');
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'ckl');
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'swl');
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'dcl');
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'swl');
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'swl');
INSERT INTO `testcounthl` VALUES ('nnnccc服务部', 'dcl');
INSERT INTO `testcounthl` VALUES ('pppxxx服务部', 'dcl');
INSERT INTO `testcounthl` VALUES ('pppxxx服务部', 'dcl');
INSERT INTO `testcounthl` VALUES ('pppxxx服务部', 'dcl');
INSERT INTO `testcounthl` VALUES ('pppxxx服务部', 'swl');
INSERT INTO `testcounthl` VALUES ('pppxxx服务部', 'swl');
- 编写相应的SQL语句实现
SELECT t.username,
count(CASE t.`code` WHEN 'dcl' or 'swl' or 'ckl' THEN 1 END)as total,
count(CASE t.`code` WHEN 'dcl' THEN 1 END)as dcl,
count(CASE t.`code` WHEN 'swl' THEN 1 END)as swl,
count(CASE t.`code` WHEN 'ckl' THEN 1 END)as ckl
FROM testcounthl t
GROUP BY t.username
得到的结果: