MySQL如何查询10月有数据但11月无对应记录的用户

原代码存在的问题 #

• 左连接后在WHERE子句中筛选A表（data表）的日期范围，会直接过滤掉左连接后A表为NULL的行，实际等效于内连接，根本无法匹配到11月无数据的用户
• 仅筛选了11月的数据，完全没有校验用户在10月是否存在非空value的记录
• SELECT *搭配GROUP BY不符合SQL标准规范，绝大多数数据库会直接抛出语法错误

推荐实现写法 #

写法1：EXISTS + NOT EXISTS 组合（逻辑最直观） #

SELECT DISTINCT B.user
FROM users B
WHERE B.permissions = 1
-- 筛选10月存在非空value的用户
AND EXISTS (
    SELECT 1 FROM data A 
    WHERE A.user = B.user 
    AND A.date BETWEEN '2021-10-01' AND '2021-10-31'
    AND A.value IS NOT NULL
)
-- 排除11月存在非空value的用户
AND NOT EXISTS (
    SELECT 1 FROM data A 
    WHERE A.user = B.user 
    AND A.date BETWEEN '2021-11-01' AND '2021-11-30'
    AND A.value IS NOT NULL
)

如果需要返回用户的其他字段，直接在最外层的SELECT后加对应列即可，不需要额外调整逻辑。

写法2：关联后分组聚合判断（适合需要同时统计两月数据的场景） #

SELECT B.user, -- 可追加其他需要返回的users表字段
       COUNT(CASE WHEN A.date BETWEEN '2021-10-01' AND '2021-10-31' AND A.value IS NOT NULL THEN 1 END) AS oct_value_cnt,
       COUNT(CASE WHEN A.date BETWEEN '2021-11-01' AND '2021-11-30' AND A.value IS NOT NULL THEN 1 END) AS nov_value_cnt
FROM users B
LEFT JOIN data A ON A.user = B.user
WHERE B.permissions = 1
GROUP BY B.user -- 按用户分组，需要返回其他users表字段的话也追加到GROUP BY后
HAVING oct_value_cnt > 0 AND nov_value_cnt = 0

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