sql - PostgreSQL-拆分行

Question

我有一个看起来像这样的表:

ID      |  name  | details
---------------------------
1.3.1-3 | Jack   | a
5.4.1-2 | John   | b
1.4.5   | Alex   | c

以及像这样拆分它的内容:

ID      |  name  | details
---------------------------
1.3.1   | Jack   | a
1.3.2   | Jack   | a
1.3.3   | Jack   | a
5.4.1   | John   | b
5.4.2   | John   | b
1.4.5   | Alex   | c

我怎样才能在 postgresql 中做到这一点？

Answer 1

CREATE TABLE tosplit
        ( id text NOT NULL
        , name text
        , details text
        );

INSERT INTO tosplit( id , name , details ) VALUES
 ( '1.3.1-3' , 'Jack' , 'a' )
,( '5.4.1-2' , 'John' , 'b' )
,( '1.4.5' , 'Alex' , 'c' )


WITH zzz AS (
        SELECT id
        , regexp_replace(id, '([0-9\.]+\.)([0-9]+)-([0-9]+)', e'\\1', e'g') AS one
        , regexp_replace(id, '([0-9\.]+\.)([0-9]+)-([0-9]+)', e'\\2', e'g') AS two
        , regexp_replace(id, '([0-9\.]+\.)([0-9]+)-([0-9]+)', e'\\3', e'g') AS three
        , name
        , details
        FROM tosplit
        )
    SELECT z1.id
        -- , z1.one
        , z1.one || generate_series( z1.two::integer, z1.three::integer)::text AS four
        , z1.name, z1.details
FROM zzz z1
WHERE z1.two <> z1.one
UNION ALL
SELECT z0.id
        -- , z0.one
        , z0.one AS four
        , z0.name, z0.details
FROM zzz z0
WHERE z0.two = z0.one
        ;

---

结果:

CREATE TABLE
INSERT 0 3
   id    | four  | name | details 
---------+-------+------+---------
 1.3.1-3 | 1.3.1 | Jack | a
 1.3.1-3 | 1.3.2 | Jack | a
 1.3.1-3 | 1.3.3 | Jack | a
 5.4.1-2 | 5.4.1 | John | b
 5.4.1-2 | 5.4.2 | John | b
 1.4.5   | 1.4.5 | Alex | c