--比如:有一张表S,记录了某公司每个季度的销售额,如下
Y Q AMT
2015 1 100
2015 2 110
2015 3 130
2015 4 100
2016 1 200
2016 2 150
2016 3 100
2016 4 300
--现在需要将这个表的内容显示为(列转行)
Y Q1 Q2 Q3 Q4
2015 100 110 130 100
2016 200 150 100 300
SELECT * FROM S;
SELECT SS.Y,SS.AMT Q1,SS.QQ Q2,SS.WW Q3,SS.EE Q4
FROM (
SELECT S.*,
LEAD(S.AMT,1)OVER(PARTITION BY S.Y ORDER BY S.Q) QQ,
LEAD(S.AMT,2)OVER(PARTITION BY S.Y ORDER BY S.Q) WW,
LEAD(S.AMT,3)OVER(PARTITION BY S.Y ORDER BY S.Q) EE
FROM S) SS
WHERE SS.Q = 1;
---利用2点:
1、一个lead()可以生成一个新的列
2、每个lead()都可以拿到后N(1,2,3,.....)个值
--小练习一把:把上面这个例子用case when 实现试一下
SELECT SS.Y,
SUM(SS.QQ) Q1,
MAX(SS.WW) Q2,
MIN(SS.EE) Q3,
AVG(SS.RR) Q4
FROM (
SELECT S.*,
CASE
WHEN Q = 1 THEN AMT
END QQ,
CASE
WHEN Q = 2 THEN AMT
END WW,
CASE S.Q
WHEN 3 THEN AMT
END EE,
CASE
WHEN Q = 4 THEN AMT
END RR
FROM S) SS
GROUP BY SS.Y;
---用到的2个点:
1、一个 CASE END 就生成一个新的列,同时可以拿到对应的列的值
2、用到聚合函数每个组返回一行结果,并且会过滤掉空值(不计算空值)
--优化一下
SELECT S.Y,
SUM(CASE
WHEN Q = 1 THEN AMT
END) Q1,
SUM(CASE
WHEN Q = 2 THEN AMT
END) WW,
SUM(CASE S.Q
WHEN 3 THEN AMT
END) EE,
SUM(CASE
WHEN Q = 4 THEN AMT
END) RR
FROM S
GROUP BY S.Y;
CREATE TABLE S(Y NUMBER(4),Q NUMBER(2),AMT NUMBER(3));
INSERT INTO S VALUES(2015,1,100);
INSERT INTO S VALUES(2015,2,110);
INSERT INTO S VALUES(2015,3,130);
INSERT INTO S VALUES(2015,4,100);
INSERT INTO S VALUES(2016,1,200);
INSERT INTO S VALUES(2016,2,150);
INSERT INTO S VALUES(2016,3,100);
INSERT INTO S VALUES(2016,4,300);
COMMIT;