myql数据库，sql横排转竖排以及竖排转横排，oracle的over函数的使用

一、引言

　　前些日子遇到了一个sql语句的横排转竖排以及竖排转横排的问题，现在该总结一下，具体问题如下：

这里的第二题和第三题和下面所讲述的学生的成绩表是相同的，这里给大家留一下一个念想，大家可以自己做做上面的笔试题。

我主要针对的是第二题和第三题来做讲解，第一题相信大家都会做，这里就不赘述了，直接进入正题！

 

二、问题详解

　　1、我们先来说说第二题，

　　（1）首先我们先创建一个表，用实际来说话，新建一个tb表，

DROP TABLE tb;
CREATE TABLE tb(
    name varchar(10),
    subject VARCHAR(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）下面我们开始竖排转横排

SELECT NAME 姓名,
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）进一步的拓展，假如我们想要的结果为下图

SELECT NAME 姓名,
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 TABLE tb1(
    姓名 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 from tb1
union
select 姓名 as name,'数学' as subject,数学 as score from tb1
union
select 姓名 as name,'物理' as subject,物理 as score from tb1
order 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 FROM tb1
)t ORDER BY NAME 

结果为：

3、下面讨论一下第三题

（1）创建表tb2

CREATE TABLE tb2(
    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 tb2

考察开窗函数的，

 想看更多over开窗函数可以查看这篇博客：http://blog.csdn.net/moshansk/article/details/52451455