时间戳

首页 > 解决方案 > Oracle SQL - Set null on repeating values

sql - Oracle SQL - Set null on repeating values

问题描述

Please help with below Oracle SQL query:

Tried several ways but didn't get expected results.

Data set:

ORDER_NO    ITEM CODE
1234      999999    777
1234      999999    111
1234      999999    777
1234      999999    111

Expected result:

ORDER_NO    ITEM     CODE
1234      999999      777
                      111

标签： sqloracle

解决方案

You can try to make row number by ROW_NUMBER window function in a CTE, then use CASE WHEN to make it.

CREATE TABLE T(
   ORDER_NO INT,
   ITEM INT,
   CODE INT
);


INSERT INTO T VALUES (1234,999999,777);
INSERT INTO T VALUES (1234,999999,111);
INSERT INTO T VALUES (1234,999999,777);
INSERT INTO T VALUES (1234,999999,111);

Query 1:

with cteRn as (
  SELECT t1.*,ROW_NUMBER() OVER (PARTITION BY ORDER_NO,ITEM,CODE order by ORDER_NO) rn 
  FROM T t1
), cteGrp as (
  SELECT
       t1.*,ROW_NUMBER() OVER (PARTITION BY ORDER_NO,ITEM ORDER BY CODE desc) grp
  FROM cteRn t1
  WHERE rn = 1
)

SELECT  
     (CASE WHEN grp = 1 then ORDER_NO end) ORDER_NO,
     (CASE WHEN grp = 1 then ITEM end)  ITEM,
     CODE 
FROM cteGrp

Results:

| ORDER_NO |   ITEM | CODE |
|----------|--------|------|
|     1234 | 999999 |  777 |
|   (null) | (null) |  111 |

推荐阅读