SQL行列转换

mingnianshimanian

浏览: 40311 次
性别:
来自: 北京

最近访客更多访客>>

ln190808

neusoft_jerry

jimieweile

fmbobin

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

数据库

sql

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

方法二：动态获取列名，就是方法一的动态化，这里使用了quotename方法，就是转义标识符

DECLARE @sql VARCHAR(8000)

SELECT @sql = isnull(@sql + ' union all ','') + ' select name, [course]='

+ quotename(Name,'''') + ' , [score] = ' + quotename(Name) + ' from tbl_grade'

FROM syscolumns

WHERE Name != 'name' AND Name != 'id' AND ID = object_id('tbl_grade')

exec(@sql + ' order by name')

go

方法三：使用unpivot

SELECT name,course,score FROM tbl_grade 

unpivot (score FOR course IN(语文,数学,英语)) t

方法四：使用unpivot的动态化

DECLARE @sql NVARCHAR(4000)

SELECT @sql = isnull(@sql + ',','') + quotename(Name)

FROM syscolumns

WHERE ID = object_id('tbl_grade') AND Name NOT IN ('name','id')

SET @sql = 'select name,[course],[score] from tbl_grade unpivot ([score] for [course] in(' + @sql + ')) b'

exec(@sql)

以上四种方法结果都一样，如下图