MySQL技术问询：如何筛选仅与指定两家公司有预订的用户并计算预订价值

我明白你的需求：要找出只和company1、company2有过预订，且从未与其他任何公司有过预订的用户，然后统计他们的所有预订价值。你当前的SQL只是筛选出了这两家公司的预订记录，但没有排除那些同时还和其他公司有预订的用户，所以需要调整逻辑。

核心思路 #

我们需要先锁定符合条件的用户：

1. 这些用户的所有预订记录对应的公司，只能是company1或company2；
2. 排除掉任何有过其他公司预订记录的用户。

方案一：使用NOT EXISTS排除不符合条件的用户 #

这种方法逻辑直观，通过子查询检查用户是否存在其他公司的预订，不存在则保留：

SELECT b.booked_date, b.price
FROM person p
JOIN booking b ON p.id = b.person_id  -- 这里假设booking表通过person_id关联用户，若你的关联字段不同请调整
JOIN company c ON b.company_id = c.id
WHERE c.name IN ('company1', 'company2')
  -- 排除存在其他公司预订的用户
  AND NOT EXISTS (
    SELECT 1
    FROM booking b_other
    JOIN company c_other ON b_other.company_id = c_other.id
    WHERE b_other.person_id = p.id
      AND c_other.name NOT IN ('company1', 'company2')
  );

方案二：使用GROUP BY+HAVING筛选用户 #

通过分组统计每个用户的预订公司情况，确保没有其他公司的记录：

-- 先筛选出符合条件的用户ID，再关联获取预订记录
SELECT b.booked_date, b.price
FROM (
    SELECT p.id
    FROM person p
    JOIN booking b ON p.id = b.person_id
    JOIN company c ON b.company_id = c.id
    GROUP BY p.id
    -- 确保该用户的所有预订公司都在指定范围内
    HAVING MAX(CASE WHEN c.name NOT IN ('company1', 'company2') THEN 1 ELSE 0 END) = 0
) AS valid_users
JOIN person p ON valid_users.id = p.id
JOIN booking b ON p.id = b.person_id
JOIN company c ON b.company_id = c.id
WHERE c.name IN ('company1', 'company2');

注意事项 #

• 请确认你的表关联字段是否正确：如果你的person和booking的关联不是p.id = b.person_id，而是原SQL中的p.booking_id = b.id，那这个表结构其实是一个用户对应一条预订，和你描述的“每个用户多条预订”矛盾，可能需要调整表关联逻辑。
• 如果需要统计每个用户的总预订价值，可以在最终查询外层加上GROUP BY p.id，并使用SUM(b.price)计算总和。

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