如何用T-SQL高效分配餐桌给家庭（遵循座位规则）

1. 规则量化与核心公式拆解 #

首先把合并餐桌的座位规则转化为可计算的公式，确保T-SQL能直接实现：
组合后的可用座位数 = 所有餐桌原始座位总和 - 扣减数
其中扣减数的计算逻辑：

• 单桌组合：扣减数=0
• 多桌组合（2-4桌）：
  • 若组合为两张3座桌：扣减数=0
  • 其他情况：每添加一张3座桌扣1，每添加一张非3座桌扣2（这里的“添加”指组合中除第一张外的后续餐桌）

示例验证：

• 6+3：原始总和9，扣减1 → 可用8，符合要求
• 6+3+3：原始总和12，扣减2 → 可用10，符合要求
• 3+3：原始总和6，扣减0 → 可用6，符合要求

2. 候选餐桌组合生成 #

由于限制使用餐桌数≤4，我们可以通过递归CTE生成所有1-4桌的合法组合（避免重复组合，如桌A+桌B与桌B+桌A视为同一组合）：

• 基础项：单桌组合，直接取餐桌的原始座位数
• 递归项：每次添加一张ID大于当前组合中最大ID的餐桌，按规则计算新组合的可用座位数，直到桌数达到4
• 将生成的组合存储到临时表（带索引优化查询），字段包括：组合餐桌列表、可用座位数、使用桌数、餐桌ID列表

3. 家庭分配的贪婪策略 #

为了平衡空位最少和减少餐桌移动的需求，采用优先分配大人数家庭的贪婪逻辑（大人数家庭可选组合更少，优先分配避免后续无合适组合）：

1. 按家庭人数从大到小排序
2. 为每个家庭筛选出所有未被使用、可用座位≥家庭人数的组合，按「空位（可用座位-家庭人数）升序」+「使用桌数升序」排序，取最优组合
3. 标记该组合中的餐桌为已使用，避免重复分配
4. 用游标或循环实现逐家庭分配（集合操作难以处理排他性分配，游标更直观）

4. 40余张餐桌的性能优化 #

40张餐桌生成1-4桌的组合总数约为10万条，完全在T-SQL的处理能力范围内，可通过以下方式优化：

• 生成组合时提前过滤可用座位小于最小家庭人数的组合，减少无效数据
• 使用临时表而非表变量，利用统计信息提升查询效率
• 为临时表建立可用座位数、餐桌ID的索引，加速组合筛选与已使用标记

5. 核心代码框架示例 #

-- 定义数据结构
DECLARE @Tables TABLE (TableID INT IDENTITY(1,1), TableName VARCHAR(50), Seats INT)
DECLARE @Families TABLE (FamilyID INT IDENTITY(1,1), FamilyName VARCHAR(50), Members INT)
-- 插入模拟数据
INSERT INTO @Tables VALUES ('T1',3),('T2',3),('T3',6),('T4',6),('T5',8)
INSERT INTO @Families VALUES ('F1',5),('F2',8),('F3',10)

-- 生成所有合法餐桌组合
;WITH TableCombos AS (
    -- 单桌组合
    SELECT 
        TableID,
        CAST(TableName AS VARCHAR(MAX)) AS TableList,
        Seats AS AvailableSeats,
        1 AS TableCount,
        CAST(TableID AS VARCHAR(MAX)) AS IDList
    FROM @Tables
    UNION ALL
    -- 多桌组合（2-4桌）
    SELECT 
        tc.TableID + t.TableID,
        tc.TableList + ',' + t.TableName,
        tc.AvailableSeats + t.Seats - 
        CASE 
            WHEN tc.TableCount + 1 = 2 AND t.Seats=3 AND tc.AvailableSeats=3 THEN 0
            WHEN t.Seats=3 THEN 1
            ELSE 2
        END AS AvailableSeats,
        tc.TableCount + 1 AS TableCount,
        tc.IDList + ',' + CAST(t.TableID AS VARCHAR(MAX)) AS IDList
    FROM TableCombos tc
    JOIN @Tables t ON t.TableID > CAST(RIGHT(tc.IDList, CHARINDEX(',', REVERSE(tc.IDList))-1) AS INT)
    WHERE tc.TableCount < 4
)
SELECT DISTINCT 
    TableList,
    AvailableSeats,
    TableCount,
    CAST(value AS INT) AS TableID
INTO #TableCombos
FROM TableCombos
CROSS APPLY STRING_SPLIT(IDList, ',')
-- 创建索引优化查询
CREATE CLUSTERED INDEX IX_AvailableSeats ON #TableCombos(AvailableSeats)
CREATE NONCLUSTERED INDEX IX_TableID ON #TableCombos(TableID)

-- 执行分配逻辑
DECLARE @UsedTables TABLE (TableID INT)
DECLARE @AllocationResults TABLE (FamilyName VARCHAR(50), Members INT, TableList VARCHAR(MAX), AvailableSeats INT, EmptySeats INT)

DECLARE @FamilyName VARCHAR(50), @Members INT
DECLARE family_cursor CURSOR FOR
    SELECT FamilyName, Members FROM @Families ORDER BY Members DESC

OPEN family_cursor
FETCH NEXT FROM family_cursor INTO @FamilyName, @Members
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 获取最优未使用组合
    SELECT TOP 1 
        tc.TableList, tc.AvailableSeats
    INTO #TempCombo
    FROM #TableCombos tc
    LEFT JOIN @UsedTables ut ON tc.TableID = ut.TableID
    WHERE ut.TableID IS NULL
    GROUP BY tc.TableList, tc.AvailableSeats, tc.TableCount
    HAVING tc.AvailableSeats >= @Members
    ORDER BY (tc.AvailableSeats - @Members) ASC, tc.TableCount ASC

    -- 标记餐桌为已使用
    INSERT INTO @UsedTables
    SELECT CAST(value AS INT) FROM STRING_SPLIT((SELECT TableList FROM #TempCombo), ',')

    -- 记录分配结果
    INSERT INTO @AllocationResults
    SELECT 
        @FamilyName, @Members, 
        (SELECT TableList FROM #TempCombo), 
        (SELECT AvailableSeats FROM #TempCombo),
        (SELECT AvailableSeats FROM #TempCombo) - @Members AS EmptySeats

    DROP TABLE #TempCombo
    FETCH NEXT FROM family_cursor INTO @FamilyName, @Members
END
CLOSE family_cursor
DEALLOCATE family_cursor

-- 查询最终分配结果
SELECT * FROM @AllocationResults
DROP TABLE #TableCombos

6. 扩展注意事项 #

• 若存在无法分配的家庭，需添加异常处理逻辑记录相关信息
• 若需考虑“减少餐桌移动”的历史分配因素，可加入历史分配表，在组合排序时优先选择历史使用过的组合
• 可根据实际需求调整贪婪策略的排序优先级（如优先选择桌数少的组合）

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