SQL 去除重複欄位 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

0

SQL 去除重複欄位

sql oracle

se7en11 2020-05-16 22:35:00 ‧ 3065 瀏覽

分享至

假設資料表如下
有三個欄位
A、B、QTY

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

主要問題是重複欄位不要顯示這部分，直覺想到用程式處理，但想尋找是否有SQL可以解決的方法

複雜一點的情況可能如下
註：第二行的C1會顯示，因為B欄已經不同了(優先順序由左至右)

登入發表討論

直播研討會

{{ item.subject }}

{{ item.channelVendor }} {{ item.webinarstarted }} |

{{ formatDate(item.duration) }}

直播中

2 個回答

1

通靈亡

iT邦高手 1 級 ‧ 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

回應 1
分享
檢舉

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

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
      )
 )

登入發表回應

0

rogeryao

iT邦超人 8 級 ‧ 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

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

回應 1
分享
檢舉

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

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

登入發表回應

我要發表回答

立即登入回答

參賽組數

1123 組

團體組數

52 組

累計文章數

23097 篇

完賽人數

656 人

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js

IT邦幫忙