iT邦幫忙

0

SQL 去除重複欄位

假設資料表如下
有三個欄位
A、B、QTY
https://ithelp.ithome.com.tw/upload/images/20200516/20118425FNSjBEb6Id.jpg

想請問,若純下SQL(Oracle)有機會辦到以下效果嗎
SUM(QTY) GROUP BY A、B欄位,以A欄位為主要群(做小計),其中重複欄位不要顯示(粉色部分)

主要問題是重複欄位不要顯示這部分,直覺想到用程式處理,但想尋找是否有SQL可以解決的方法
https://ithelp.ithome.com.tw/upload/images/20200516/20118425jrx3Oy9V11.jpg

複雜一點的情況可能如下
註:第二行的C1會顯示,因為B欄已經不同了(優先順序由左至右)
https://ithelp.ithome.com.tw/upload/images/20200516/20118425GMjGR7Gl38.jpg

2 個回答

1
通靈亡
iT邦研究生 3 級 ‧ 2020-05-17 00:40:36
最佳解答

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 上一列資料的日期

LAG Function (上一列的某一個欄位值)

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

LEAD Function (下一列的某一個欄位值)

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
se7en11 iT邦新手 5 級 ‧ 2020-05-17 10:49:40 檢舉

LAG、LEAD 好好用/images/emoticon/emoticon02.gif
假設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
      )
 )    
0
rogeryao
iT邦大師 3 級 ‧ 2020-05-17 08:30:49

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

Demo

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

Demo

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

Demo

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

Demo

se7en11 iT邦新手 5 級 ‧ 2020-05-17 10:58:02 檢舉

太感謝了,忘了還可以用ROW_NUMBER排

我要發表回答

立即登入回答