PostgreSQL中如何将单条数据按金额拆分插入多行？

原表结构与数据 #

现有test_tbl表，结构及初始化数据如下：

CREATE TABLE test_tbl(id integer, num integer, name text);

INSERT INTO test_tbl (id, num, name)
VALUES(1, 2000, 'fdghgf'),(2, 3000, 'fdghg'),(3, 400, 'fhgf'),(4, 2200, 'dhgf');

目标表结构 #

需要将处理后的数据插入到tbl_bifuraction表，表结构定义如下：

CREATE TABLE tbl_bifuraction(id integer, num integer, name text, is_bifurcated integer);

需求说明 #

对test_tbl中的数据进行处理后插入目标表：

• 若num值≤1000，直接保留原记录，is_bifurcated设为0
• 若num值>1000，拆分为多条num=1000的记录，以及一条剩余金额的记录（剩余金额为0时不生成该行），所有拆分后的记录is_bifurcated设为1

处理后目标表的预期数据如下：

id  num  name   is_bifurcated
1   1000 fdghgf 1
1   1000 fdghgf 1
2   1000 fdghg  1
2   1000 fdghg  1
2   1000 fdghg  1
3   400  fhgf   0
4   1000 dhgf   1
4   1000 dhgf   1
4   200  dhgf   1

实现SQL #

以下是基于PostgreSQL的实现代码，利用generate_series生成拆分所需的行数，结合条件判断处理剩余金额：

INSERT INTO tbl_bifuraction (id, num, name, is_bifurcated)
SELECT
    t.id,
    CASE
        WHEN gs.n < t.num / 1000 THEN 1000
        ELSE t.num % 1000
    END AS num,
    t.name,
    CASE WHEN t.num > 1000 THEN 1 ELSE 0 END AS is_bifurcated
FROM test_tbl t
LEFT JOIN generate_series(1, (t.num + 999) / 1000) gs(n) ON true
WHERE CASE WHEN gs.n = (t.num + 999) / 1000 THEN t.num % 1000 ELSE 1000 END <> 0
ORDER BY t.id, num DESC;

代码说明 #

1. generate_series(1, (t.num + 999) / 1000)：根据num计算需要拆分的总行数，比如2000对应2行，2200对应3行
2. CASE语句判断每行的num值：前num/1000行设为1000，最后一行设为剩余金额
3. WHERE条件过滤掉剩余金额为0的情况（比如num刚好是1000的倍数时，最后一行剩余为0，不插入）
4. is_bifurcated字段根据原num是否大于1000设置1或0

内容的提问来源于stack exchange，提问作者codeanonym