SQL进阶-行转列&列转行-白红宇

SQL进阶-行转列&列转行

阅读量：5220 次

发布时间：2019-06-14

本文共 12034 字，大约阅读时间需要 40 分钟。

一、行转列

1、建表

CREATE TABLE score(    student_id VARCHAR(20) NOT NULL COMMENT '学生编号' DEFAULT '',    student_name VARCHAR(50) NOT NULL COMMENT '学生姓名' DEFAULT '',    gender VARCHAR(10) NOT NULL COMMENT '学生性别' DEFAULT '',    subject_name VARCHAR(50) NOT NULL COMMENT '课程名称' DEFAULT '',    score INTEGER NOT NULL COMMENT '分数' DEFAULT 0)ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='学生成绩';DELETE FROM score;INSERT INTO score VALUES('S001','张三','男','高等数学',82);INSERT INTO score VALUES('S001','张三','男','计算机导论',67);INSERT INTO score VALUES('S001','张三','男','概率论',90);INSERT INTO score VALUES('S001','张三','男','机械原理',82);INSERT INTO score VALUES('S002','李四','男','高等数学',78);INSERT INTO score VALUES('S002','李四','男','计算机导论',76);INSERT INTO score VALUES('S002','李四','男','概率论',65);INSERT INTO score VALUES('S002','李四','男','几何学',43);INSERT INTO score VALUES('S003','王五','女','计算机导论',88);INSERT INTO score VALUES('S003','王五','女','概率论',98);INSERT INTO score VALUES('S003','王五','女','几何学',85);INSERT INTO score VALUES('S004','赵六','男','高等数学',84);INSERT INTO score VALUES('S004','赵六','男','计算机导论',76);INSERT INTO score VALUES('S004','赵六','男','机械原理',65);INSERT INTO score VALUES('S004','赵六','男','几何学',48);INSERT INTO score VALUES('S005','孙七','女','高等数学',34);INSERT INTO score VALUES('S005','孙七','女','计算机导论',91);INSERT INTO score VALUES('S005','孙七','女','概率论',82);INSERT INTO score VALUES('S005','孙七','女','机械原理',56);INSERT INTO score VALUES('S005','孙七','女','几何学',70);

2、利用max(CASE ... WHEN ... THEN .. ELSE END) AS ""的方式来实现

##利用max(CASE ... WHEN ... THEN .. ELSE END) AS 的方式来实现##判断如果是这门学科，就取它的成绩，否则赋值为0，然后在成绩与0里取最大值SELECT   student_id,  student_name,  MAX(CASE WHEN subject_name = '高等数学' THEN score ELSE 0 END) AS '高等数学',  MAX(CASE WHEN subject_name = '计算机导论' THEN score ELSE 0 END) AS '计算机导论',  MAX(CASE WHEN subject_name = '概率论' THEN score ELSE 0 END) AS '概率论',  MAX(CASE WHEN subject_name = '机械原理' THEN score ELSE 0 END) AS '机械原理',  MAX(CASE WHEN subject_name = '几何学' THEN score ELSE 0 END) AS '几何学' FROM scoreGROUP BY   student_id,  student_nameORDER BY   student_id,  student_name;

3、求男女生各科平均成绩

##平均成绩肯定是总分除以人数，但是呢，有的学生没有某一门学科的成绩，我们把它变成0，##变成0其实是不妥当的，因为变成0，在做除法的时候，也会算成一个人，这样除的结果就会不准确##所以应该把0变成nullSELECT   gender,  AVG(CASE WHEN subject_name = '高等数学' THEN score ELSE NULL END) AS '高等数学',  AVG(CASE WHEN subject_name = '计算机导论' THEN score ELSE NULL END) AS '计算机导论',  AVG(CASE WHEN subject_name = '概率论' THEN score ELSE NULL END) AS '概率论',  AVG(CASE WHEN subject_name = '机械原理' THEN score ELSE NULL END) AS '机械原理',  AVG(CASE WHEN subject_name = '几何学' THEN score ELSE NULL END) AS '几何学' FROM scoreGROUP BY   genderORDER BY   gender;总结：    行转列，分组(GROUP BY)的列必须是除需要行转列之外的业务主键。    例如tb表中业务主键应该是cname和cource,但是cource需要进行行转列，所以需要按照cname分组。

4、子查询的方式，求男女生各科成绩平均值

##SELECT  gender,   MAX(CASE WHEN subject_name = '高等数学' THEN score_avg ELSE 0 END) AS '高等数学',  MAX(CASE WHEN subject_name = '计算机导论' THEN score_avg ELSE 0 END) AS '计算机导论',  MAX(CASE WHEN subject_name = '概率论' THEN score_avg ELSE 0 END) AS '概率论',  MAX(CASE WHEN subject_name = '机械原理' THEN score_avg ELSE 0 END) AS '机械原理',  MAX(CASE WHEN subject_name = '几何学' THEN score_avg ELSE 0 END) AS '几何学' FROM(SELECT   gender,  subject_name,  AVG(score) AS score_avgFROM scoreGROUP BY gender,subject_name)aGROUP BY genderORDER BY gender;

5、GROUP_CONCAT

GROUP_CONCAT()：该函数返回带有来自一个分组的连接的非NULL值的字符串结果。SELECTstudent_id,student_name,GROUP_CONCAT(subject_name) AS 课程名称, GROUP_CONCAT(score) AS 分数FROM scoreGROUP BY student_id,student_name;

结果：

二、列转行

1、建表

###employee_sales表：CREATE TABLE employee_sales(    employee_id VARCHAR(20) NOT NULL COMMENT '员工编号' DEFAULT '',    employee_name VARCHAR(50) NOT NULL COMMENT '员工姓名' DEFAULT '',    sales_amount_q1  DECIMAL(18,2) NOT NULL COMMENT '一季度销售金额' DEFAULT 0,    sales_amount_q2  DECIMAL(18,2) NOT NULL COMMENT '二季度销售金额' DEFAULT 0,    sales_amount_q3  DECIMAL(18,2) NOT NULL COMMENT '三季度销售金额' DEFAULT 0,    sales_amount_q4  DECIMAL(18,2) NOT NULL COMMENT '四季度销售金额' DEFAULT 0)ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='员工销售表';INSERT INTO employee_sales VALUES('S001','张三',273193.41,84149.96,711798.18,847930.56);INSERT INTO employee_sales VALUES('S002','李四',861586.57,819398.89,496788.47,1060.11);INSERT INTO employee_sales VALUES('S003','王五',943899.97,793446.45,351139,640459.72);INSERT INTO employee_sales VALUES('S004','赵六',585158.08,493034.64,576246.61,188906.14);###employee_assessment表：CREATE TABLE employee_assessment(    employee_id VARCHAR(20) NOT NULL COMMENT '员工编号' DEFAULT '',    employee_name VARCHAR(50) NOT NULL COMMENT '员工姓名' DEFAULT '',    assessment_level CHAR(4) NOT NULL COMMENT '季度考核等级' DEFAULT '',    sales_amount  VARCHAR(500) NOT NULL COMMENT '销售金额' DEFAULT '')ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='员工考核表';INSERT INTO employee_assessment VALUES('S001','张三','AABC','273193.41,84149.96,711798.18,847930.56');INSERT INTO employee_assessment VALUES('S002','李四','CBAB','861586.57,819398.89,496788.47,1060.11');INSERT INTO employee_assessment VALUES('S003','王五','BBAC','943899.97,793446.45,351139,640459.72');INSERT INTO employee_assessment VALUES('S004','赵六','BACA','585158.08,493034.64,576246.61,188906.14');

2、UNION ALL 列转行

##union all 合并多个子集##加了一个季度字段，方便查看select employee_id,employee_name,'一季度' as 季度,sales_amount_q1 from employee_salesunion allselect employee_id,employee_name,'二季度' as 季度,sales_amount_q2 from employee_salesUNION ALLSELECT employee_id,employee_name,'三季度' as 季度,sales_amount_q3 from employee_salesUNION ALLselect employee_id,employee_name,'四季度' as 季度,sales_amount_q4 from employee_salesORDER BY employee_id;

3、列转行2

##先看employee_assessment表数据：+-------------+---------------+------------------+-----------------------------------------+| employee_id | employee_name | assessment_level | sales_amount                            |+-------------+---------------+------------------+-----------------------------------------+| S001        | 张三          | AABC             | 273193.41,84149.96,711798.18,847930.56  || S002        | 李四          | CBAB             | 861586.57,819398.89,496788.47,1060.11   || S003        | 王五          | BBAC             | 943899.97,793446.45,351139,640459.72    || S004        | 赵六          | BACA             | 585158.08,493034.64,576246.61,188906.14 |+-------------+---------------+------------------+-----------------------------------------+##用union all列转行：select employee_id,employee_name,assessment_level,'1' as 季度,right(left(assessment_level,1),1) from employee_assessment union allselect employee_id,employee_name,assessment_level,'2' as 季度,right(left(assessment_level,2),1) from employee_assessment union allselect employee_id,employee_name,assessment_level,'3' as 季度,right(left(assessment_level,3),1) from employee_assessment union allselect employee_id,employee_name,assessment_level,'4' as 季度,right(left(assessment_level,4),1) from employee_assessment;结果：+-------------+---------------+------------------+--------+-----------------------------------+| employee_id | employee_name | assessment_level | 季度   | right(left(assessment_level,1),1) |+-------------+---------------+------------------+--------+-----------------------------------+| S001        | 张三          | AABC             | 1      | A                                 || S002        | 李四          | CBAB             | 1      | C                                 || S003        | 王五          | BBAC             | 1      | B                                 || S004        | 赵六          | BACA             | 1      | B                                 || S001        | 张三          | AABC             | 2      | A                                 || S002        | 李四          | CBAB             | 2      | B                                 || S003        | 王五          | BBAC             | 2      | B                                 || S004        | 赵六          | BACA             | 2      | A                                 || S001        | 张三          | AABC             | 3      | B                                 || S002        | 李四          | CBAB             | 3      | A                                 || S003        | 王五          | BBAC             | 3      | A                                 || S004        | 赵六          | BACA             | 3      | C                                 || S001        | 张三          | AABC             | 4      | C                                 || S002        | 李四          | CBAB             | 4      | B                                 || S003        | 王五          | BBAC             | 4      | C                                 || S004        | 赵六          | BACA             | 4      | A                                 |+-------------+---------------+------------------+--------+-----------------------------------+上面用union all虽然可以得到结果，但是当数据多的时候，就要写很多行union all，不太好？

可以用两个表的笛卡尔积：

##建sequence 表：此表中的数据要和要拆分的字段数相同：CREATE TABLE sequence (seq_num INT);INSERT INTO sequenceVALUES    (1),    (2),    (3),    (4);##right和left函数组合使用，获取某一位字符：

加where条件是因为，现在assessment_level字段正好和sequence表中的字段数相等，但是当两个字段数不等时，比如assessment_level只有三个，

加上where就可以起过滤作用，如：length(assessment_level) = 3,3已经不>=seq_num了，就算seq_num中有4，也不会select出来；select employee_id,employee_name,assessment_level,seq_num as 季度,right(left(assessment_level,seq_num),1)from employee_assessment,sequence where length(assessment_level) >= seq_num;结果：+-------------+---------------+------------------+--------+-----------------------------------------+| employee_id | employee_name | assessment_level | 季度   | right(left(assessment_level,seq_num),1) |+-------------+---------------+------------------+--------+-----------------------------------------+| S001        | 张三          | AABC             |      1 | A                                       || S001        | 张三          | AABC             |      2 | A                                       || S001        | 张三          | AABC             |      3 | B                                       || S001        | 张三          | AABC             |      4 | C                                       || S002        | 李四          | CBAB             |      1 | C                                       || S002        | 李四          | CBAB             |      2 | B                                       || S002        | 李四          | CBAB             |      3 | A                                       || S002        | 李四          | CBAB             |      4 | B                                       || S003        | 王五          | BBAC             |      1 | B                                       || S003        | 王五          | BBAC             |      2 | B                                       || S003        | 王五          | BBAC             |      3 | A                                       || S003        | 王五          | BBAC             |      4 | C                                       || S004        | 赵六          | BACA             |      1 | B                                       || S004        | 赵六          | BACA             |      2 | A                                       || S004        | 赵六          | BACA             |      3 | C                                       || S004        | 赵六          | BACA             |      4 | A                                       |+-------------+---------------+------------------+--------+-----------------------------------------+上面已经很简便的得到结果了，其实mysql还有一个函数：substring函数，能实现right和left相同的功能；##substring函数，结果应该和上面一样select employee_id,employee_name,assessment_level,seq_num as 季度,substring(assessment_level,seq_num,1)from employee_assessment,sequence where length(assessment_level) >= seq_num;

上面其实只是处理了assessment_level字段，sales_amount 字段还没有处理，值是以逗号分隔的：

##mysql有这样一个函数：substring_index函数  ##substring_index有三个参数：第一个是：要分隔的字段，第二个是：以什么分隔，第三个是：要取第几个逗号前面的数据##还嵌套了一层substring_index，-1：从右边往前取一个，以逗号分隔##这里的where，是先计算出逗号的个数，然后加1，在去和sequence表中的数据比较select employee_id,employee_name,assessment_level,seq_num,sales_amount,substring_index(substring_index(sales_amount,',',seq_num),',',-1)from employee_assessment,sequenceWHERE LENGTH(sales_amount) - LENGTH(REPLACE(sales_amount,',',''))+1 >= seq_numORDER BY employee_id;

转载于:https://www.cnblogs.com/weiyiming007/p/11458521.html

你可能感兴趣的文章

UITabbarController的UITabbarItem（例："我的"）点击时，判断是否登录