一、引言
前些日子遇到了一个sql语句的横排转竖排以及竖排转横排的问题,现在该总结一下,具体问题如下:
这里的第二题和第三题和下面所讲述的学生的成绩表是相同的,这里给大家留一下一个念想,大家可以自己做做上面的笔试题。
我主要针对的是第二题和第三题来做讲解,第一题相信大家都会做,这里就不赘述了,直接进入正题!
二、问题详解
1、我们先来说说第二题,
(1)首先我们先创建一个表,用实际来说话,新建一个tb表,
DROP TABLEtb;CREATE TABLEtb(
namevarchar(10),
subjectVARCHAR(10),
score NUMERIC
);INSERT INTO tb(name,SUBJECT,score) VALUES(‘张三‘,‘语文‘,74);INSERT INTO tb(name,SUBJECT,score) VALUES(‘张三‘,‘数学‘,83);insert into tb(Name , Subject , score) values(‘张三‘ ,‘物理‘ , 93);insert into tb(Name , Subject , score) values(‘李四‘ , ‘语文‘ , 74);insert into tb(Name , Subject , score) values(‘李四‘ , ‘数学‘ , 84);insert into tb(Name , Subject , score) values(‘李四‘ , ‘物理‘ , 94);SELECT * FROM tb;
(2)最初的查询结果如图所示
(3)下面我们开始竖排转横排
SELECTNAME 姓名,MAX(CASE SUBJECT WHEN ‘语文‘ THEN score ELSE 0 END) 语文,MAX(CASE SUBJECT WHEN ‘数学‘ THEN score ELSE 0 END) 数学,MAX(CASE SUBJECT WHEN ‘物理‘ THEN score ELSE 0 END) 物理FROM tb GROUP BY NAME
结果是:
(4)进一步的拓展,假如我们想要的结果为下图
SELECTNAME 姓名,MAX(CASE SUBJECT WHEN ‘语文‘ THEN score ELSE 0 END) 语文,MAX(CASE SUBJECT WHEN ‘数学‘ THEN score ELSE 0 END) 数学,MAX(CASE SUBJECT WHEN ‘物理‘ THEN score ELSE 0 END) 物理,SUM(score) AS总分,AVG(score) AS平均分FROM tb GROUP BY NAME
2、下面来讨论一下横排转竖排的问题
(1)首先创建表tb1,
CREATE TABLEtb1(
姓名VARCHAR(10),
语文 NUMERIC,
数学 NUMERIC,
物理 NUMERIC
);insert into tb1(姓名 , 语文 , 数学 , 物理) values(‘张三‘,74,83,93);insert into tb1(姓名 , 语文 , 数学 , 物理) values(‘李四‘,74,84,94);SELECT * FROM tb1;
如图所示:
(2)横排转竖排
方法一:
select 姓名 as name,‘语文‘ as subject,语文 as score fromtb1union
select 姓名 as name,‘数学‘ as subject,数学 as score fromtb1union
select 姓名 as name,‘物理‘ as subject,物理 as score fromtb1order by name
方法二:
SELECT * FROM(SELECT 姓名 as NAME,‘语文‘ AS SUBJECT, 语文 AS score from tb1 UNION
SELECT 姓名 AS NAME,‘数学‘ AS SUBJECT , 数学 AS score from tb1 UNION
SELECT 姓名 AS NAME,‘物理‘ AS SUBJECT, 物理 AS score FROMtb1
)tORDER BY NAME
结果为:
3、下面讨论一下第三题
(1)创建表tb2
CREATE TABLEtb2(YEAR NUMBER,
salary NUMBER
);INSERT INTO tb2(YEAR,salary) VALUES(2000,1000);INSERT INTO tb2(YEAR,salary) VALUES(2001,2000);INSERT INTO tb2(YEAR,salary) VALUES(2002,3000);INSERT INTO tb2(YEAR,salary) VALUES(2003,4000);SELECT * FROM tb2;
如图:
(2)利用over函数完成所需要求,
select year,sum(salary) over(order by salary) from tb
考察开窗函数的,
想看更多over开窗函数可以查看这篇博客:http://blog.csdn.net/moshansk/article/details/52451455
原文:http://www.cnblogs.com/lixiaochao/p/6475568.html