MS SQL Server：拆分逗号分隔SlotIds并关联两表查询

表结构假设 #

基于需求，假设两张表的核心字段如下：

• Table-1（Slots）：存储Slot基础信息，包含 SlotId（主键）、SlotDate（Slot所属日期）、SlotTime（时段）等字段
• Table-2（Bookings）：存储预约记录，包含 BookingId、SlotIds（逗号分隔的SlotId集合）、BookingDate（预约日期）等字段

以下是不同数据库的实现方案，均实现拆分SlotIds后关联两表，指定日期查询Slot详情，未关联的Slot状态显示Available：

MySQL 方案（8.0+） #

方法1：使用JSON_TABLE（推荐，性能更优） #

SELECT 
    s.SlotId,
    s.SlotTime,
    s.SlotDate,
    COALESCE(b.BookingId, 'Available') AS Status
FROM 
    `Table-1` s
LEFT JOIN (
    -- 拆分Table-2的SlotIds为独立行
    SELECT 
        JSON_UNQUOTE(jt.SlotId) AS SlotId,
        b.BookingId
    FROM 
        `Table-2` b
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(b.SlotIds, ',', '","'), '"]'),
        '$[*]' COLUMNS (SlotId VARCHAR(50) PATH '$')
    ) jt
    WHERE b.BookingDate = '2024-05-20' -- 指定预约日期
) b ON s.SlotId = b.SlotId
WHERE s.SlotDate = '2024-05-20' -- 指定Slot日期
ORDER BY s.SlotId;

方法2：递归CTE实现（兼容早期8.0版本） #

WITH SplitSlots AS (
    SELECT 
        SUBSTRING_INDEX(SlotIds, ',', 1) AS SlotId,
        SUBSTRING(SlotIds, LOCATE(',', SlotIds) + 1) AS RemainingSlots,
        BookingId
    FROM `Table-2`
    WHERE BookingDate = '2024-05-20' AND SlotIds != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(RemainingSlots, ',', 1) AS SlotId,
        SUBSTRING(RemainingSlots, LOCATE(',', RemainingSlots) + 1) AS RemainingSlots,
        BookingId
    FROM SplitSlots
    WHERE RemainingSlots != ''
)
SELECT 
    s.SlotId,
    s.SlotTime,
    s.SlotDate,
    COALESCE(ss.BookingId, 'Available') AS Status
FROM `Table-1` s
LEFT JOIN SplitSlots ss ON s.SlotId = ss.SlotId
WHERE s.SlotDate = '2024-05-20'
ORDER BY s.SlotId;

SQL Server 方案（2016+） #

利用内置STRING_SPLIT函数拆分字符串：

SELECT 
    s.SlotId,
    s.SlotTime,
    s.SlotDate,
    COALESCE(b.BookingId, 'Available') AS Status
FROM 
    [Table-1] s
LEFT JOIN (
    SELECT 
        value AS SlotId,
        b.BookingId
    FROM [Table-2] b
    CROSS APPLY STRING_SPLIT(b.SlotIds, ',')
    WHERE b.BookingDate = '2024-05-20'
) b ON s.SlotId = b.SlotId
WHERE s.SlotDate = '2024-05-20'
ORDER BY s.SlotId;

PostgreSQL 方案 #

通过STRING_TO_ARRAY+UNNEST拆分字符串：

SELECT 
    s.SlotId,
    s.SlotTime,
    s.SlotDate,
    COALESCE(b.BookingId, 'Available') AS Status
FROM 
    "Table-1" s
LEFT JOIN (
    SELECT 
        unnest(string_to_array(b.SlotIds, ',')) AS SlotId,
        b.BookingId
    FROM "Table-2" b
    WHERE b.BookingDate = '2024-05-20'
) b ON s.SlotId = b.SlotId
WHERE s.SlotDate = '2024-05-20'
ORDER BY s.SlotId;

核心逻辑说明 #

1. 先将Table-2中逗号分隔的SlotIds拆分为独立行，形成可关联的SlotId集合
2. 用左连接关联Table-1，确保所有指定日期的Slot都被查询到
3. 通过COALESCE函数判断是否存在关联的预约记录，未关联则返回Available作为状态

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