使用SQL中的拆分记录

Question

我有一张具有以下记录

前缀	以下前缀	开始端部	div	行业
1AB	00001	99999	li 1ab	ri
1AB	00527	00527	00528 mhug	ri

我想将其分配为

前缀	ri 1 ab ri ri 1	end	div	行业
1AB	00001	99999	lpsd	ri
1ab	00527 00527	00528	mhug	ri
1 ab	ri 1 ab ri	00527	1ab	ri

ab 00529999999999999999999999999999999999999999999.如果您看到的是因为我们有一个具有相同前缀的记录，但开始和结束是不同的，我想将第一个记录分为2个记录，一个从00001到00526，再到00529，再到99999

。 SQL查询

Answer 1

我认为这有效（但是它需要更大的数据集来正确测试）：

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;

输出：

前缀	begin	end	div	行业
1ab	00001	00527	lpsd	ri
1ab	00527	00528	mhug	ri
1ab	00528	99999	lpsd	ri

db＆lt;＆lt;＆gt;＆gt; gt; gt; gt; A3F630E04D8DBBB rel =“ nofollow noreferrer”>此处

Answer 2

首先，在保留单词后（例如开始，结束...），命名列并不是一个好习惯。可以使用这样的模型子句来解决这...

    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

希望它会有所帮助。问候...