SQL行列转换使用较为频繁,面试当中也经常有这样的题目,汇总了mysql 和sql server几种常见的方法,如有错误请指正。
MYSQL行转列
CREATE TABLE `tbl_grade` ( `id` bigint(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `course` varchar(10) DEFAULT NULL, `score` decimal(5,0) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('张三', '语文', '74'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('张三', '数学', '55'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('张三', '英语', '66'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('李四', '语文', '88'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('李四', '数学', '76'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('李四', '英语', '69'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('王五', '语文', '11'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('王五', '数学', '45'); INSERT INTO tbl_grade (`name`, `course`, `score`) VALUES ('王五', '英语', '68');
方法一:使用表链接
SELECT DISTINCT a.name, (SELECT score FROM tbl_grade b WHERE a.name = b.name AND b.course = '语文') AS '语文', (SELECT score FROM tbl_grade b WHERE a.name = b.name AND b.course = '数学') AS '数学', (SELECT score FROM tbl_grade b WHERE a.name = b.name AND b.course = '英语') AS '英语', SUM(score) AS '总分', cast(avg(score*1.0) as decimal(18,2)) as '平均分' FROM tbl_grade a GROUP BY a.name
方法二:使用分组,对每个分组分别处理
SELECT DISTINCT a.name, SUM(CASE course WHEN '语文' THEN score END) AS '语文', SUM(CASE course WHEN '数学' THEN score END) AS '数学', SUM(CASE course WHEN '英语' THEN score END) AS '英语', SUM(score) AS '总分', cast(avg(score*1.0) as decimal(18,2)) as '平均分' FROM tbl_grade a GROUP BY a.name
也可以把case when then 换成if
SELECT DISTINCT a.name, SUM(IF (course = '语文' , score , null ) ) as '语文', SUM(IF (course = '数学' , score , null ) ) as '数学', SUM(IF (course = '英语' , score , null ) ) as '英语', SUM(score) AS '总分', cast(avg(score*1.0) as decimal(18,2)) as '平均分' FROM tbl_grade a GROUP BY a.name
方法四:使用了存储过程,其实是第二种方法的动态化,先计算出所有课程的数量,然后对每个分组进行课程查询。
很明显前两种方法属于硬编码,增加课程后就需要修改SQL。而第三种则不需要修改SQL。
#MySQL中不能在一个存储过程中删除另一个存储过程 #DROP PROCEDURE IF EXISTS sp_count(); DELIMITER && CREATE PROCEDURE sp_count() BEGIN #课程名称 DECLARE course_name VARCHAR(20); #所有课程数量 DECLARE count INT; #计数器 DECLARE i INT DEFAULT 0; #拼接SQL字符串 SET count = (SELECT COUNT(distinct course) FROM tbl_grade); SET @s = 'SELECT name'; WHILE i < count DO SET course_name = (SELECT course FROM tbl_grade LIMIT i,1); SET @s = CONCAT(@s,',SUM(CASE course WHEN ','\'',course_name,'\'',' THEN score END )',' AS ','\'',course_name,'\''); SET i = i+1; END WHILE; SET @s = CONCAT(@s,',sum(score) as 总分,cast(avg(score*1.0) as decimal(18,2)) as 平均分 FROM tbl_grade GROUP BY name'); PREPARE stmt FROM @s; EXECUTE stmt; END && call sp_count();
以上方法处理结果都一样如下图
MYSQL列转行
CREATE TABLE tbl_course( `id` BIGINT(10) not NULL AUTO_INCREMENT, `name` VARCHAR(10) DEFAULT NULL, `语文` DECIMAL(5,0) DEFAULT null, `数学` DECIMAL(5,0) DEFAULT null, `英语` DECIMAL(5,0) DEFAULT null, PRIMARY KEY (`id`) ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8
INSERT INTO tbl_course (`name`,`语文`,`数学`,`英语`) VALUES ('张三','77','44','22'); INSERT INTO tbl_course (`name`,`语文`,`数学`,`英语`) VALUES ('李四','55','12','85'); INSERT INTO tbl_course (`name`,`语文`,`数学`,`英语`) VALUES ('王五','85','45','96');
方法一:手动列举列,然后union数据
SELECT tepTable.* FROM ( SELECT name, "语文", 语文 as score FROM tbl_course union all SELECT name, "数学", 数学 as score FROM tbl_course union all SELECT name, "英语", 英语 as score FROM tbl_course ) tepTable ORDER BY tepTable.`name`
方法二:使用存储过程,从MySQL schem获取表最新表字段,拼接SQL。实际上是方法一的动态扩展。
DELIMITER $$ CREATE PROCEDURE sp_trans() BEGIN #课程名称 DECLARE course_n VARCHAR(20); #所有课程数量 DECLARE count INT; #计数器 DECLARE i INT DEFAULT 0; #拼接SQL字符串 SET @s = 'SELECT * FROM ('; SET count = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME='tbl_course' AND COLUMN_NAME <> 'name' AND COLUMN_NAME <> 'id'); WHILE i < count DO SET course_n = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME='tbl_course' AND COLUMN_NAME <> 'name' AND COLUMN_NAME <> 'id' LIMIT i,1); SET @s = CONCAT(@s, ' SELECT name, "'); SET @s = CONCAT(@s, course_n); SET @s = CONCAT(@s, '", '); SET @s = CONCAT(@s, course_n); SET @s = CONCAT(@s,' as score FROM tbl_course '); IF i <> count-1 THEN SET @s = CONCAT(@s, 'union all'); END IF; SET i = i+1; END WHILE; SET @s = CONCAT(@s, ' ) tepTable ORDER BY tepTable.`name`'); PREPARE stmt FROM @s; EXECUTE stmt; END $$ CALL sp_trans
2种方法查询结果都一样,如下图
MSSQL行转列
if exists (select * from sysobjects where id = object_id('tbl_grade') and type = 'U') drop table tbl_grade go CREATE TABLE tbl_grade ( id bigint NOT NULL identity, name varchar(10) DEFAULT NULL, course varchar(10) DEFAULT NULL, score decimal(5,0) DEFAULT NULL, PRIMARY KEY (id) ) insert into tbl_grade VALUES ('张三','语文',74) insert into tbl_grade VALUES ('张三','数学',83) insert into tbl_grade VALUES ('张三','英语',93) insert into tbl_grade VALUES ('李四','语文',74) insert into tbl_grade VALUES ('李四','数学',84) insert into tbl_grade VALUES ('李四','英语',94) insert into tbl_grade VALUES ('王五','语文',54) insert into tbl_grade VALUES ('王五','数学',63) insert into tbl_grade VALUES ('王五','英语',72) SELECT * FROM tbl_grade
SELECT name,SUM(语文) AS 语文,SUM(数学) AS 数学,SUM(英语) AS 英语 FROM tbl_grade g pivot( MAX(score) FOR course IN (语文,数学,英语)) a GROUP BY name
MSSQL列转行
if exists (select * from sysobjects where id = object_id('tbl_grade') and type = 'U') drop table tbl_grade go CREATE TABLE tbl_grade ( id bigint NOT NULL identity, name varchar(10) DEFAULT NULL, 语文 decimal(5,0) DEFAULT NULL, 数学 decimal(5,0) DEFAULT NULL, 英语 decimal(5,0) DEFAULT NULL, PRIMARY KEY (id) ) INSERT INTO tbl_grade (name,语文,数学,英语) VALUES ('张三',99,44,22) INSERT INTO tbl_grade (name,语文,数学,英语) VALUES ('李四',55,12,85) INSERT INTO tbl_grade (name,语文,数学,英语) VALUES ('王五',85,45,96)
方法一:静态union all
SELECT * FROM ( SELECT name,course='语文',score = 语文 FROM tbl_grade UNION ALL SELECT name,course='数学',score = 数学 FROM tbl_grade UNION ALL SELECT name,course='英语',score = 英语 FROM tbl_grade ) t ORDER BY name,CASE course WHEN '语文' THEN 1 WHEN '数学' THEN 2 WHEN '英语' THEN 3 end
相关推荐
sql试题及答案,sql 行列转换,sql存储过程实例
sql行列转换扩展 明源内部学习资料
oracle sql行列转换
SQL行列转换 Pivot UnPivot
sql行列转换、一个字段包含另一个字段.sql
sql行列转换[借鉴].pdf
SQL语句行列转换(附带数据库、表、视图操作) ,不错的文档。
一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A...
本人在工作中遇到了涉及到数据库行列之间相互转换的问题,在网上搜索了很久也没有一个比较完整的解释,通过本人自己的摸索测试,整理出来了Oracle中SQL语句行列之间相互转换的资料,大家可以下载学习。
sql server 行列转换.
假设有张学生成绩表(tb)如下: Name Subject Result 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 */ ----------------------------------------------------------------...
动态 实现 SQL 2008行列转换的pivot
1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
java语言 数据库 mysql数据库 sql行列转换的例子 详解
Sql语句实现表的行列转换,行转列,列转行
sqlserver行列转换,实现行列转换,全部脚本,测试通过