SQL-1454：使用JOIN与子查询查询活跃用户结果不一致的问题

题目为SQL-1454：查询活跃用户，活跃用户定义为连续5天及以上登录账户的用户，需返回其id和name并按id排序。

我使用JOIN关联Accounts表的写法得到错误输出，但正确答案采用子查询方式得到预期结果，无法理解两者结果差异的原因。

题目示例输入输出 #

Input: 
Accounts table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+
Logins table:
+----+------------+
| id | login_date |
+----+------------+
| 7  | 2020-05-30 |
| 1  | 2020-05-30 |
| 7  | 2020-05-31 |
| 7  | 2020-06-01 |
| 7  | 2020-06-02 |
| 7  | 2020-06-02 |
| 7  | 2020-06-03 |
| 1  | 2020-06-07 |
| 7  | 2020-06-10 |
+----+------------+
Output: 
+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+

我的代码 #

SELECT DISTINCT a.id, a.name
FROM Accounts a
LEFT JOIN Logins l
ON a.id = l.id
JOIN Logins l1
ON l.id=l1.id AND DATEDIFF(l.login_date, l1.login_date) BETWEEN 1 AND 4
GROUP BY l.login_date
HAVING COUNT(DISTINCT l1.login_date) = 4

测试输入 #

{"headers":{"Accounts":["id","name"],"Logins":["id","login_date"]},"rows":{"Accounts":[[182,"Gavriel"],[119,"Naftali"],[31,"Yaakov"],[136,"Menachem"],[142,"Sarah"],[204,"Daniel"],[49,"Ezra"],[27,"David"]],"Logins":[[142,"2020-6-27"],[119,"2020-6-29"],[31,"2020-6-26"],[27,"2020-6-27"],[182,"2020-7-2"],[136,"2020-6-28"],[142,"2020-7-5"],[27,"2020-6-29"],[136,"2020-6-27"],[49,"2020-7-1"],[204,"2020-7-1"],[49,"2020-7-5"],[204,"2020-7-3"],[49,"2020-7-3"],[31,"2020-7-3"],[204,"2020-7-3"],[142,"2020-6-30"],[119,"2020-6-26"],[142,"2020-6-29"],[136,"2020-7-2"],[49,"2020-7-2"],[182,"2020-7-4"],[119,"2020-6-29"],[49,"2020-6-30"],[136,"2020-7-5"],[27,"2020-7-2"],[136,"2020-6-28"],[31,"2020-6-29"],[204,"2020-7-3"],[142,"2020-6-29"],[31,"2020-6-30"],[204,"2020-6-27"],[204,"2020-7-2"],[182,"2020-6-27"],[31,"2020-7-3"],[119,"2020-7-4"],[142,"2020-6-27"],[119,"2020-6-27"],[27,"2020-6-26"],[142,"2020-7-2"],[27,"2020-6-28"],[136,"2020-6-26"],[119,"2020-6-27"],[142,"2020-7-1"],[27,"2020-7-1"],[31,"2020-6-29"],[204,"2020-6-28"],[136,"2020-6-28"],[204,"2020-7-3"],[31,"2020-6-28"],[182,"2020-6-29"],[49,"2020-7-4"],[204,"2020-6-27"],[136,"2020-7-5"],[142,"2020-7-4"],[31,"2020-7-2"],[182,"2020-7-1"],[204,"2020-6-28"],[31,"2020-7-4"],[136,"2020-7-1"],[136,"2020-6-26"],[27,"2020-7-4"],[27,"2020-6-29"],[31,"2020-7-2"]]}}

我的输出 #

{"headers": ["id", "name"], "values": [[49, "Ezra"], [136, "Menachem"], [142, "Sarah"], [182, "Gavriel"]]}

预期输出 #

{"headers":["id","name"],"values":[[49,"Ezra"]]}

正确代码 #

SELECT DISTINCT l1.id,
(SELECT name FROM Accounts WHERE id = l1.id) AS name
FROM Logins l1
JOIN Logins l2 ON l1.id = l2.id AND DATEDIFF(l2.login_date, l1.login_date) BETWEEN 1 AND 4
GROUP BY l1.id, l1.login_date
HAVING COUNT(DISTINCT l2.login_date) = 4

两种写法的核心差异在于分组逻辑、关联范围和统计针对性：

1. 分组维度错误
   你的代码按l.login_date分组，会把所有用户的同日期登录记录混在一起统计，只要某一天存在任意用户能匹配到4个后续日期的登录，就会触发条件，导致误判其他用户符合要求。而正确代码按l1.id, l1.login_date分组，针对单个用户的每个登录日期单独校验，确保只统计该用户自身的连续登录情况。

2. 关联逻辑导致跨用户匹配
   你用LEFT JOIN Accounts后再关联Logins l1，未严格限制用户ID的匹配范围，可能出现用户A的登录日期匹配到用户B的后续登录记录，错误判定用户A满足连续登录条件。正确代码直接从Logins表关联，通过l1.id = l2.id严格限定只匹配同一用户的登录数据，避免了跨用户的错误关联。

3. 统计逻辑不绑定用户
   你的HAVING条件统计的是分组内（按日期分组）的不同日期数量，不关联具体用户；正确代码的统计是针对单个用户的起始登录日期，统计该用户在之后4天内的不同登录日期数，只有数量为4时，才说明该用户从该起始日开始有连续5天的登录（起始日+后续4天）。

另外，你的代码中LEFT JOIN没有实际意义，后续的JOIN Logins l1会过滤掉无登录记录的账户，最终效果等同于INNER JOIN。

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