MS SQL Server查询：为选配菜匹配关联主餐

首先假设你的事务表名为TransactionItems，核心字段包括：

• TransactionID：唯一标识同一订单/事务的ID
• ItemID：餐品项的唯一ID
• ItemName：餐品名称（主餐无前置“配”字，选配菜以“配”开头，比如“配薯条”）
• 其他业务字段（如OrderTime、Quantity等）

实现步骤： #

1. 标记主餐与选配菜：通过ItemName判断是否为主餐（可根据实际业务规则调整，比如有专门的IsMainMeal字段则直接用）
2. 生成主餐分组ID：使用累计窗口函数，每遇到一个主餐就递增分组ID，让后续所有选配菜归属于该分组
3. 生成RowWithinTxn：在每个事务+主餐分组内，按餐品的下单顺序生成行号
4. 关联主餐名称：在每个分组内取主餐的名称作为AssociatedMainMeal

完整SQL语句 #

WITH MarkedMeals AS (
    SELECT 
        *,
        -- 标记是否为主餐：这里假设主餐名称不含“配”字，选配菜以“配”开头，可根据实际调整规则
        CASE WHEN LEFT(ItemName, 1) <> '配' THEN 1 ELSE 0 END AS IsMainMeal
    FROM TransactionItems
),
MealGroups AS (
    SELECT 
        *,
        -- 生成主餐分组ID：同一事务内，累计主餐的数量作为分组标识
        SUM(IsMainMeal) OVER (PARTITION BY TransactionID ORDER BY ItemID, OrderTime) AS MainMealGroup
    FROM MarkedMeals
)
SELECT 
    TransactionID,
    ItemID,
    ItemName,
    -- 生成事务内的组内行号
    ROW_NUMBER() OVER (PARTITION BY TransactionID, MainMealGroup ORDER BY ItemID, OrderTime) AS RowWithinTxn,
    -- 获取当前分组的主餐名称
    MAX(CASE WHEN IsMainMeal = 1 THEN ItemName END) OVER (PARTITION BY TransactionID, MainMealGroup) AS AssociatedMainMeal,
    -- 保留其他原有字段
    OrderTime,
    Quantity
FROM MealGroups
ORDER BY TransactionID, ItemID, OrderTime;

关键说明 #

• 主餐判断规则：如果你的表有专门的IsMainMeal字段（比如bit类型），直接替换CASE WHEN LEFT(ItemName, 1) <> '配' THEN 1 ELSE 0 END为该字段即可，无需依赖名称判断
• 排序依据：ORDER BY ItemID, OrderTime是为了保证餐品的顺序正确，确保选配菜确实跟随对应的主餐，可根据实际的下单顺序字段调整
• 分组逻辑：SUM(IsMainMeal) OVER (...)会在同一事务内，每遇到一个主餐就把分组ID加1，这样后续的选配菜都会继承这个分组ID，直到下一个主餐出现，完美解决多选配菜的匹配问题

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