MySQL多对多关系过滤：筛选无巡演/录制状态喜爱乐队的用户

嘿，我来帮你搞定这个筛选用户的SQL查询问题！你的需求是找出所有喜爱的乐队都未处于巡演（touring）或录制（recording）状态的用户，包括那些没有喜爱任何乐队的用户对吧？我给你两种清晰可行的方案，你可以根据自己的数据库环境和数据量来选择：

方案一：用NOT EXISTS子查询（逻辑直观，性能优） #

这种方式直接排除掉那些有任意一支关联乐队处于目标状态的用户，逻辑非常清晰，数据库执行起来也比较高效：

SELECT u.*, COUNT(bu.band_id) AS band_count
FROM users u
LEFT JOIN band_users bu ON bu.user_id = u.id
LEFT JOIN bands b ON bu.band_id = b.id
WHERE NOT EXISTS (
    -- 子查询：检查当前用户是否有乐队处于touring/recording状态
    SELECT 1
    FROM band_users bu_sub
    JOIN bands b_sub ON bu_sub.band_id = b_sub.id
    WHERE bu_sub.user_id = u.id
    AND b_sub.status IN ('touring', 'recording')
)
GROUP BY u.id;

• 这里的LEFT JOIN会保留没有任何喜爱乐队的用户，他们的band_count会显示为0
• 如果只想保留至少有一支喜爱乐队的用户，把LEFT JOIN改成INNER JOIN即可

方案二：用GROUP BY + HAVING聚合筛选 #

如果你更习惯用聚合函数来做筛选，这种方式也很实用：

SELECT u.*, COUNT(b.id) AS band_count
FROM users u
LEFT JOIN band_users bu ON bu.user_id = u.id
LEFT JOIN bands b ON bu.band_id = b.id
GROUP BY u.id
-- 统计用户关联的乐队中处于目标状态的数量，等于0则符合要求
HAVING SUM(CASE WHEN b.status IN ('touring', 'recording') THEN 1 ELSE 0 END) = 0;

• CASE WHEN会把每支符合状态的乐队标记为1，不符合的为0，SUM之后如果是0，说明该用户所有乐队都不在目标状态里
• 同样，要排除无乐队的用户，可以在HAVING里加AND COUNT(b.id) > 0

小提示 #

• 确保users.id是主键，这样GROUP BY u.id后SELECT u.*在大多数数据库（比如MySQL、PostgreSQL）里都是合法的
• 如果数据量很大，NOT EXISTS的方案通常会比聚合筛选更快，因为数据库可以在找到第一个符合条件的乐队后就终止子查询

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