Oracle 可以用 LAG Function 或 LEAD Function
得到上一列或下一列的某個欄位資料
在欄位上寫Case When 相同顯示Null,不相同顯示值
例如:
CASE
WHEN LAG (order_date,1) OVER (ORDER BY order_date) = order_date
THEN NULL
ELSE order_date
END AS 上一列資料的日期
https://www.techonthenet.com/oracle/functions/lag.php
SELECT product_id,
order_date AS 目前資料的日期,
LAG (order_date,1) OVER (ORDER BY order_date) AS 上一列資料的日期
FROM orders;
PRODUCT_ID | 目前資料的日期 | 上一列資料的日期 |
---|---|---|
1000 | 2007/09/25 | NULL |
2000 | 2007/09/26 | 2007/09/25 |
1000 | 2007/09/27 | 2007/09/26 |
2000 | 2007/09/28 | 2007/09/27 |
2000 | 2007/09/29 | 2007/09/28 |
1000 | 2007/09/30 | 2007/09/29 |
https://www.techonthenet.com/oracle/functions/lead.php
SELECT product_id,
order_date AS 目前資料的日期,
LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS 下一列資料的日期
FROM orders;
PRODUCT_ID | 目前資料的日期 | 下一列資料的日期 |
---|---|---|
1000 | 2007/09/25 | 2007/09/27 |
1000 | 2007/09/27 | 2007/09/30 |
1000 | 2007/09/30 | NULL |
2000 | 2007/09/26 | 2007/09/28 |
2000 | 2007/09/28 | 2007/09/29 |
2000 | 2007/09/29 | NULL |
LAG、LEAD 好好用
假設Table為TB,看起來可以解
SELECT *
FROM
(
SELECT CASE WHEN LAG(A,1) OVER (PARTITION BY '' ORDER BY A) = A THEN ''
ELSE A
END AS A,
CASE WHEN LAG(B,1) OVER (PARTITION BY A ORDER BY A,B) = B THEN ''
ELSE B
END AS B,
CASE WHEN LAG(C,1) OVER (PARTITION BY A,B ORDER BY A,B,C) = C THEN ''
ELSE C
END AS C,
QTY
FROM
(
SELECT A,B,C,SUM(QTY) AS QTY
FROM TB
WHERE 1=1
GROUP BY A,B,C
UNION ALL
SELECT A || ' SubTotal' as A,'' AS B,'' AS C, SUM(QTY) AS QTY
FROM TB
GROUP BY A
)
)
Oracle case 1 :
CREATE TABLE TEST01(A VARCHAR2(10),B VARCHAR2(40),QTY number(10));
INSERT INTO TEST01(A,B,QTY) VALUES('A1','B1',10);
INSERT INTO TEST01(A,B,QTY) VALUES('A1','B2',20);
INSERT INTO TEST01(A,B,QTY) VALUES('A1','B1',30);
INSERT INTO TEST01(A,B,QTY) VALUES('A2','B2',40);
INSERT INTO TEST01(A,B,QTY) VALUES('A2','B3',10);
SELECT
CASE WHEN AORDER=1 THEN A ELSE '' END AS A,B,QTY1 AS QTY,
A AS ATEMP,AORDER
FROM (
SELECT A,B,SUM(QTY) AS QTY1,
ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) AS AORDER
FROM TEST01
WHERE 1=1
GROUP BY A,B
) "TEMP1"
WHERE 1=1
--
UNION
SELECT A,'' AS B,SUM(QTY) AS QTY1,
A AS ATEMP,COUNT(1)+1 AORDER
FROM TEST01
WHERE 1=1
GROUP BY A
ORDER BY ATEMP,AORDER,B
Oracle case 2 :
CREATE TABLE TEST01(A VARCHAR2(10),B VARCHAR2(40),C VARCHAR2(40),QTY number(10));
INSERT INTO TEST01(A,B,C,QTY) VALUES('A1','B1','C1',10);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A1','B1','C1',30);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A1','B2','C1',20);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A1','B2','C2',15);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A1','B3','C1',10);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A1','B3','C4',5);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A2','B2','C1',40);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A2','B3','C1',10);
INSERT INTO TEST01(A,B,C,QTY) VALUES('A2','B3','C4',5);
SELECT
CASE WHEN AORDER=1 THEN A ELSE '' END AS A,
CASE WHEN BORDER=1 THEN B ELSE '' END AS B,C,QTY1 AS QTY,
A AS ATEMP,AORDER,
B AS BTEMP,BORDER
FROM (
SELECT A,B,C,SUM(QTY) AS QTY1,
ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) AS AORDER,
ROW_NUMBER() OVER(PARTITION BY A,B ORDER BY A,B,C) AS BORDER
FROM TEST01
WHERE 1=1
GROUP BY A,B,C
) "TEMP1"
WHERE 1=1
--
UNION
SELECT A,'' AS B,'' AS C,SUM(QTY) AS QTY1,
A AS ATEMP,COUNT(1)+1 AORDER,
'' AS BTEMP,
0 AS BORDER
FROM TEST01
WHERE 1=1
GROUP BY A
ORDER BY ATEMP,AORDER,BTEMP,BORDER,C
MSSQL case 1 :
CREATE TABLE TEST01(A VARCHAR(10),B VARCHAR(40),QTY INT);
--
INSERT INTO TEST01
VALUES ('A1','B1',10),
('A1','B2',20),
('A1','B1',30),
('A2','B2',40),
('A2','B3',10);
SELECT
CASE WHEN AORDER=1 THEN A ELSE '' END AS A,B,QTY1 AS QTY,
A AS ATEMP,AORDER
FROM (
SELECT A,B,SUM(QTY) AS QTY1,
ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) AS AORDER
FROM TEST01
WHERE 1=1
GROUP BY A,B
) AS TEMP1
WHERE 1=1
--
UNION
SELECT A,'' AS B,SUM(QTY) AS QTY1,
A AS ATEMP,COUNT(1)+1 AORDER
FROM TEST01
WHERE 1=1
GROUP BY A
ORDER BY ATEMP,AORDER,B
MSSQL case 2 :
CREATE TABLE TEST01(A VARCHAR(10),B VARCHAR(40),C VARCHAR(40),QTY INT);
--
INSERT INTO TEST01
VALUES ('A1','B1','C1',10),
('A1','B1','C1',30),
('A1','B2','C1',20),
('A1','B2','C2',15),
('A1','B3','C1',10),
('A1','B3','C4',5),
--
('A2','B2','C1',40),
('A2','B3','C1',10),
('A2','B3','C4',5);
SELECT
CASE WHEN AORDER=1 THEN A ELSE '' END AS A,
CASE WHEN BORDER=1 THEN B ELSE '' END AS B,C,QTY1 AS QTY,
A AS ATEMP,AORDER,
B AS BTEMP,BORDER
FROM (
SELECT A,B,C,SUM(QTY) AS QTY1,
ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) AS AORDER,
ROW_NUMBER() OVER(PARTITION BY A,B ORDER BY A,B,C) AS BORDER
FROM TEST01
WHERE 1=1
GROUP BY A,B,C
) AS TEMP1
WHERE 1=1
--
UNION
SELECT A,'' AS B,'' AS C,SUM(QTY) AS QTY1,
A AS ATEMP,COUNT(1)+1 AORDER,
'' AS BTEMP,
0 AS BORDER
FROM TEST01
WHERE 1=1
GROUP BY A
ORDER BY ATEMP,AORDER,BTEMP,BORDER,C