使用SQL拆分表中的記錄

我有一張包含以下記錄的表格

我想將其拆分如下

如果您看到因為我們有一條前綴相同但開頭和結尾不同的記錄，我想將第一條記錄拆分為 2 條記錄，一條從 00001 到 00526 和 00529 到 99999。

是否可以使用 SQL 查詢執行相同的操作

uj5u.com熱心網友回復：

我認為這可行（但它需要更大的資料集才能正確測驗）：

SELECT prefix,
       "BEGIN",
       "END",
       CASE type
       WHEN 1
       THEN div
       ELSE LAG(CASE type WHEN 1 THEN div END) IGNORE NULLS
              OVER (PARTITION BY prefix, industry, grp ORDER BY "BEGIN")
       END AS div,
       industry
FROM   (
  SELECT prefix,
         div,
         industry,
         value AS "BEGIN",
         type,
         SUM(type) OVER (PARTITION BY prefix, industry ORDER BY value) AS grp,
         LEAD(value) OVER (PARTITION BY prefix, industry ORDER BY value) AS "END"
  FROM   table_name
  UNPIVOT (value FOR type IN ("BEGIN" AS 1, "END" AS -1))
)
WHERE  grp > 0
ORDER BY prefix, industry, "BEGIN";

其中，對于樣本資料：

CREATE TABLE table_name (prefix, "BEGIN", "END", div, industry) AS
SELECT '1AB', '00001', '99999', 'LPSD', 'RI' FROM DUAL UNION ALL
SELECT '1AB', '00527', '00528', 'MHUG', 'RI' FROM DUAL;

輸出：

字首	開始	結尾	磁區	行業
1AB	00001	00527	LPSD	RI
1AB	00527	00528	MHUG	RI
1AB	00528	99999	LPSD	RI

db<>在這里擺弄

uj5u.com熱心網友回復：

首先，在保留字（如 Begin、End ...）之后命名列并不是一個好習慣。這可以使用像這樣的 MODEL 子句來解決...

    WITH
        tbl AS
            (   Select '1AB' "PRFX", '00001' "BGN", '99999' "ND", 'LPSD' "DV", 'RI' "IND" From Dual UNION ALL
                Select '1AB' "PRFX", '00527' "BGN", '00528' "ND", 'MHUG' "DV", 'RI' "IND" From Dual
            ) 
    SELECT DISTINCT
        m.*,
        t.DV "DV",
        t.IND "IND"
    FROM
        (
    SELECT
        INDX,
        PRFX, BGN, ND
    FROM
        (   SELECT
                PRFX "PRFX",
                LISTAGG(BGN, ',') WITHIN GROUP (ORDER BY PRFX, BGN) "BGN",
                LISTAGG(ND, ',') WITHIN GROUP (ORDER BY PRFX, ND) "ND"
            FROM
                tbl 
            GROUP BY
                PRFX
        )
    MODEL
        DIMENSION BY(0 as INDX)
        MEASURES (PRFX, BGN, ND)
            RULES ITERATE(3) 
                (   PRFX[ITERATION_NUMBER 1] = PRFX[0],
                    BGN[ITERATION_NUMBER 1] = Nvl(SubStr(REPLACE(BGN[0], ',', ''), (ITERATION_NUMBER*5)   1, 5), LPAD(To_Number(SubStr(REPLACE(ND[0], ',', ''), ((ITERATION_NUMBER-2)*5)   1, 5))   1, 5, '0')),
                    ND[ITERATION_NUMBER 1] = CASE 
                                                WHEN SubStr(REPLACE(BGN[0], ',', ''), ((ITERATION_NUMBER 1)*5)   1, 5) < SubStr(REPLACE(ND[0], ',', ''), (ITERATION_NUMBER*5)   1, 5) THEN
                                                    LPAD(To_Number(SubStr(REPLACE(BGN[0], ',', ''), ((ITERATION_NUMBER 1)*5)   1, 5)) - 1, 5, '0')
                                              ELSE 
                                                  SubStr(REPLACE(ND[0], ',', ''), ((ITERATION_NUMBER-1)*5)   1, 5) 
                                              END
                )
        ) m
    LEFT JOIN
        (   SELECT 
                PRFX, DV, 
                IND, 
                Max(ND) OVER(PARTITION BY PRFX, DV ORDER BY PRFX, DV) "MAX_ND",
                Min(BGN) OVER(PARTITION BY PRFX, DV ORDER BY PRFX, DV) "MIN_BGN"
            FROM 
                tbl
        )  t ON (t.PRFX = m.PRFX And (t.MAX_ND = m.ND OR t.MIN_BGN = m.BGN)) 
WHERE
    m.INDX > 0 And ND Is Not Null
ORDER BY 
    m.INDX
    --
    -- Result
    -- INDX PRFX BGN    ND      DV      IND
    -- 1    1AB  00001  00526   LPSD    RI
    -- 2    1AB  00527  00528   MHUG    RI
    -- 3    1AB  00529  99999   LPSD    RI

希望它會有所幫助。問候...

標籤：sql 甲骨文 oracle11g 数据仓库

上一篇：從DOM(JS)中洗掉多個子項

下一篇：有沒有辦法在Oracle中宣告狀態和級別？[復制]