代码之家 › 专栏 › 技术社区 › Jay Shankar Gupta

SQL根据某种逻辑重新分配秩

db2 sql

Jay Shankar Gupta · 技术社区 · 6 年前

flg “E”表示现有,“N”表示“新”。

LOC_RANK    b       c       d       rownum  flg
---------   ------  ------  ------  ------  ---
1           (null)  (null)  (null)  1       E
1           a       b       c       2       N
2           a       b       c       3       E
2           (null)  (null)  (null)  4       N
3           (null)  (null)  (null)  5       E
3           (null)  (null)  (null)  6       N
4           a       b       c       7       E
4           a1      b1      c1      8       N
5           a       a       c       9       E 
5           a       a       c       10      N

http://sqlfiddle.com/#!18/226bc/5

要求:

案例1: 所以对于军衔来说 1 flg公司 价值电子在表if中 flg公司 价值如果没有空值,则loc\u秩应该相同。

案例2: 2 在表if中 flg公司 电子列和列 2 flg公司 不如果为空值,则loc_rank应更改为下一个递增值。

3 在表if中 flg公司 价值 a,c,d为空列和列 三 flg公司 不如果有空值,那么loc\u秩应该是相同的。

案例4: 所以对于军衔来说 4 flg公司 价值电子 a,c,d的值不为null 列和列 4 在表if中价值不并且没有空值,但如果a,b,c在这两种情况下相同,那么loc\u秩应该相同,否则下一个递增的值。

LOC_RANK    b       c       d       rownum  flg
---------   ------  ------  ------  ------  ---
1           (null)  (null)  (null)  1       E
1           a       b       c       2       N
2           a       b       c       3       E
3           (null)  (null)  (null)  4       N
4           (null)  (null)  (null)  5       E
4           (null)  (null)  (null)  6       N
5           a       b       c       7       E
6           a1      b1      c1      8       N
7           a       a       c       9       E 
7           a       a       c       10      N

1 回复 | 直到 6 年前

Nishant Gupta 6 年前

您的问题解决方案:

;with cte
AS
(
SELECT
T1.LOC_RANK AS LOC_RANK1,
T1.b AS b1,
T1.c AS c1,
T1.d AS d1,
T1.rownum AS rownum1,
T1.flg AS flg1,
T2.LOC_RANK AS LOC_RANK2,
T2.b AS b2,
T2.c AS c2,
T2.d AS d2,
T2.rownum AS rownum2,
T2.flg AS flg2,  
CASE WHEN (T1.b IS NOT NULL AND T1.c IS NOT NULL AND T1.d IS NOT NULL)
      AND ( 
                (T2.b IS NULL AND T2.c IS NULL AND T2.d IS NULL)
                 OR
                 (T2.b IS NOT NULL AND T2.c IS NOT NULL AND T2.d IS NOT NULL
                  AND T1.b <> T2.b AND T1.c <> T2.c AND T1.d <> T2.d)
           )
     THEN 1
     ELSE 0
END AS Change     
FROM Table1 T1
INNER JOIN TABLE1 T2
ON T1.LOC_RANK = T2.LOC_RANK
AND T1.flg <> T2.flg AND T1.flg <> 'N'
)
, cte2 
AS
(
SELECT 
LOC_RANK1,
b1,
c1,
d1,
rownum1,
flg1,
LOC_RANK2,
b2,
c2,
d2,
rownum2,
flg2,  
Change,
sum(Change) OVER(ORDER BY rownum2) AS cum_change
FROM cte
)
, finalcte
AS
(
SELECT
CASE WHEN LOC_RANK1 <> 1
      THEN LOC_RANK1 + lag(cum_change) OVER (ORDER BY rownum1)
      ELSE LOC_RANK1
END AS LOC_Rank,      
b1 AS b,
c1 AS c,
d1 AS d,
rownum1 AS rownum,
flg1 AS flg
FROM 
cte2
UNION ALL
SELECT
CASE WHEN LOC_RANK2 = 1 
      THEN 
      CASE WHEN CHANGE = 1
            THEN LOC_RANK2 + CHANGE
            ELSE LOC_RANK2
      END      
      ELSE
        LOC_RANK2 + cum_change
END AS LOC_RANK,        
b2,
c2,
d2,
rownum2,
flg2
FROM 
cte2
)
SELECT *
FROM finalcte
ORDER BY LOC_Rank, flg

LOC_Rank       b       c       d      rownum    flg
    1        (null)  (null)  (null)    1         E
    1          a       b       c       2         N
    2          a       b       c       3         E
    3        (null)  (null)  (null)    4         N
    4        (null)  (null)  (null)    5         E
    4        (null)  (null)  (null)    6         N
    5          a       b       c       7         E
    6          a1      b1      c1      8         N
    7          a       a       c       9         E
    7          a       a       c       10        N

http://sqlfiddle.com/#!18/f750b/32