多表关联SQL查询：筛选可接单服务商ID的技术咨询

一、满足条件的SQL查询 #

完全可以通过单条SQL实现需求，核心思路是先统计每个服务商在其每个可用时间段内的未完成订单数，再与该时间段的可接单人数（no_of_users）做对比，筛选出符合条件的服务商ID。下面用CTE（公共表表达式）来拆分逻辑，让查询更易读：

WITH vendor_uncompleted_bookings AS (
    SELECT
        v.vendor_id,
        v.id AS available_time_id,
        COUNT(b.id) AS uncompleted_count
    FROM vendor_available_time v
    LEFT JOIN bookings b
        ON v.vendor_id = b.vendor_id
        AND b.booking_datetime BETWEEN v.date_start_time AND v.date_end_time
        AND b.booking_status NOT IN ('completed', 'rejected', 'cancelled')
    GROUP BY v.vendor_id, v.id
)
SELECT DISTINCT vu.vendor_id AS user_id
FROM vendor_uncompleted_bookings vu
JOIN vendor_available_time v
    ON vu.vendor_id = v.vendor_id
    AND vu.available_time_id = v.id
WHERE v.no_of_users > vu.uncompleted_count;

查询逻辑说明： #

• CTE部分：关联vendor_available_time和bookings表，统计每个服务商在其每个可用时间段内的未完成订单数（状态不在完成/拒绝/取消的订单）。用LEFT JOIN确保即使某个时间段没有订单，也能统计到0。
• 主查询：将统计结果与vendor_available_time关联，筛选出可接单人数大于未完成订单数的服务商ID，并用DISTINCT去重（避免同一个服务商因多个符合条件的时间段被重复输出）。

如果你的数据库不支持CTE（比如MySQL 5.7及以前版本），可以改用子查询实现：

SELECT DISTINCT v.vendor_id AS user_id
FROM vendor_available_time v
LEFT JOIN (
    SELECT
        b.vendor_id,
        COUNT(b.id) AS uncompleted_count,
        vat.id AS available_time_id
    FROM bookings b
    JOIN vendor_available_time vat
        ON b.vendor_id = vat.vendor_id
        AND b.booking_datetime BETWEEN vat.date_start_time AND vat.date_end_time
    WHERE b.booking_status NOT IN ('completed', 'rejected', 'cancelled')
    GROUP BY b.vendor_id, vat.id
) b_stats ON v.id = b_stats.available_time_id
WHERE v.no_of_users > COALESCE(b_stats.uncompleted_count, 0);

二、是否可通过单条查询实现？ #

当然可以！上面提供的两种写法都是单条SQL查询，通过CTE或子查询将统计和筛选逻辑整合在一起，不需要拆分多条语句执行。

三、数据库结构是否需要调整？ #

从你给出的表结构示例来看，有几个关键优化点：

1. 修复主键唯一性问题 #

• vendor_available_time和bookings表的id字段作为主键，示例中出现了重复值（比如多条记录id都是1），这违反了主键的唯一性约束，必须修正为每条记录对应唯一的id（比如自增主键或UUID）。

2. 解决时间段重叠风险 #

示例中服务商2在2019-10-16同时存在“全天”和特定时段的可用时间记录，这会导致订单统计时被重复计算（比如10点的订单会同时匹配两个时间段）。建议：

• 在业务层限制服务商不能录入重叠的时间段；
• 或者在表中添加约束，确保同一个服务商的可用时间段无重叠；
• 若必须支持重叠时间段，需要调整统计逻辑避免订单重复计数。

3. 优化状态字段存储 #

booking_status用字符串存储，建议改用枚举类型（如MySQL的ENUM、PostgreSQL的ENUM）或数字编码（比如1=accepted，2=work in progress，3=completed等），既节省存储空间，又能避免拼写错误，还能提升查询效率。

4. 添加索引提升性能 #

为了加快查询速度，建议添加以下索引：

• bookings表：(vendor_id, booking_datetime, booking_status)复合索引，加速订单统计时的关联和筛选；
• vendor_available_time表：(vendor_id, date_start_time, date_end_time)复合索引，加速时间段匹配。

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