SQL交易表按带售价的组合表头分组子项的实现问询

可以实现，以下是针对两种分组需求的SQL实现（假设你的交易表名为transaction_table）：

原交易表结构 #

Lineitem |  ItemCode  | Item Desc               | sale price |
 1       |   CB1      |     COMBO HEADER MENU1  |  5000      |
 2       |   100      |     Item A              |     0      |
 3       |   101      |     Item B              |     0      |
 4       |   CB2      |     COMBO HEADER MENU2  | 10000      |
 5       |   102      |     Item C              |     0      |

一、生成数字序号分组（对应期望结果1） #

通过累计组合表头的数量，为每组分配递增的数字序号：

WITH combo_groups AS (
    SELECT 
        Lineitem,
        ItemCode,
        "Item Desc" AS ItemDesc,
        -- 累计组合表头的数量，形成分组序号
        SUM(CASE WHEN ItemCode LIKE 'CB%' THEN 1 ELSE 0 END) OVER (ORDER BY Lineitem) AS group_id
    FROM transaction_table
)
SELECT 
    ItemCode,
    ItemDesc,
    group_id AS "Group"
FROM combo_groups
ORDER BY Lineitem;

执行后结果：

ItemCode   |    Item Desc           | Group |
 CB1       | COMBO HEADER MENU1     | 1     |
 100       | Item A                 | 1     |
 101       | Item B                 | 1     |
 CB2       | COMBO HEADER MENU2     | 2     |
 102       | Item C                 | 2     |

二、生成组合表头ItemCode作为分组（对应期望结果2） #

使用窗口函数获取最近的组合表头ItemCode，向下填充作为分组标识：

WITH combo_groups AS (
    SELECT 
        Lineitem,
        ItemCode,
        "Item Desc" AS ItemDesc,
        -- 取当前行及之前最近的组合表头ItemCode
        LAST_VALUE(CASE WHEN ItemCode LIKE 'CB%' THEN ItemCode ELSE NULL END) 
            OVER (ORDER BY Lineitem ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_code
    FROM transaction_table
)
SELECT 
    ItemCode,
    ItemDesc,
    group_code AS "Group"
FROM combo_groups
ORDER BY Lineitem;

执行后结果：

ItemCode   |    Item Desc           | Group   |
 CB1       | COMBO HEADER MENU1     | CB1     |
 100       | Item A                 | CB1     |
 101       | Item B                 | CB1     |
 CB2       | COMBO HEADER MENU2     | CB2     |
 102       | Item C                 | CB2     |

兼容旧版本SQL方言的备选方案 #

如果你的数据库不支持LAST_VALUE，可以先生成数字分组ID，再关联组合表头的ItemCode：

WITH combo_headers AS (
    SELECT 
        SUM(CASE WHEN ItemCode LIKE 'CB%' THEN 1 ELSE 0 END) OVER (ORDER BY Lineitem) AS group_id,
        ItemCode
    FROM transaction_table
    WHERE ItemCode LIKE 'CB%'
),
all_rows AS (
    SELECT 
        Lineitem,
        ItemCode,
        "Item Desc" AS ItemDesc,
        SUM(CASE WHEN ItemCode LIKE 'CB%' THEN 1 ELSE 0 END) OVER (ORDER BY Lineitem) AS group_id
    FROM transaction_table
)
SELECT 
    a.ItemCode,
    a.ItemDesc,
    c.ItemCode AS "Group"
FROM all_rows a
JOIN combo_headers c ON a.group_id = c.group_id
ORDER BY a.Lineitem;

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