SQL 语句包含在相关子查询中没有匹配项的行

Question

我有下面的相关子查询。 table1中的每个id（147,148,149）在table2中都有很多记录。然而，id 148 没有与语句中的时间条件匹配的记录。因此它不包含在输出中。我希望在计数列中包含 0。需要改变什么？

SELECT b.fkid, COUNT(DISTINCT username)
FROM table2 AS b
WHERE
b.fkid IN ( 147,148,149 )
AND time > (SELECT SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) FROM table1 AS a WHERE a.id = b.fkid)
GROUP BY b.fkid

该语句返回：

b.fkid   COUNT(DISTINCT username)
147      41
149      26

我希望它返回：

b.fkid   COUNT(DISTINCT username)
147      41
148       0
149      26

好的，我找到了解决方案。这是 rexem 答案的修改版本：

SELECT t.fkid,
   IFNULL(nu.num_users, 0)
FROM TABLE_2 t
LEFT JOIN (SELECT t.fkid,
                  COUNT(DISTINCT t.username) 'num_users'
           FROM TABLE_2 t
           JOIN TABLE_1 a ON a.id = t.fkid
                          AND SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) < t.time
           GROUP BY t.fkid) nu ON nu.fkid = t.fkid
WHERE t.fkid IN (147, 148, 149)
GROUP BY t.fkid, nu.num_users

Changes from rexem's answer:
"SEC_TO_TIME( 60*60 )) = t.time" to "SEC_TO_TIME( 60*60 )) < t.time"
Removed "t.time" in GROUP BY clause of subquery

Answer 1

尝试：

   SELECT t.fkid,
          IFNULL(nu.num_users, 0)
     FROM TABLE_2 t
LEFT JOIN (SELECT t.fkid,
                  COUNT(DISTINCT t.username) 'num_users'
             FROM TABLE_2 t
             JOIN TABLE_1 a ON a.id = t.fkid
                           AND SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) = t.time
         GROUP BY t.fkid) nu ON nu.fkid = t.fkid
   WHERE t.fkid IN (147, 148, 149)
GROUP BY t.fkid, nu.num_users

Answer 2

尝试使用 COALESCE 来处理时间与空集进行比较的场景（例如，因为 table1 和 table2 之间没有匹配项）：

SELECT b.fkid, COUNT(DISTINCT username)
FROM table2 AS b
WHEREb.fkid IN ( 147,148,149 )
AND time > COALESCE((SELECT SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) FROM table1 AS a WHERE a.id = b.fkid), 0)
GROUP BY b.fkid

COALESCE 接受一组无限制的参数，并且从该组中返回第一个非空参数。

Answer 3

SELECT a.ID, COUNT(DISTINCT username)
FROM table1 AS a LEFT JOIN table2 AS b
on a.ID = b.fkID
WHERE a.ID IN ( 147,148,149 )
AND b.time > SUBTIME(a.endTime, SEC_TO_TIME( 60*60 )) 
GROUP BY a.ID

无论如何，这有帮助吗？

编辑：我不确定这是否可以在 mysql 中工作。但是，我希望您了解使用它来构建查询。

Answer 4

我认为将零带入结果的最佳机会是使用（不相交的）UNION。第一部分是您当前的查询；第二个子句将查找没有相关条目的值，并为计数列选择常量 0。

通常，外连接就可以解决问题。这里的困难在于外连接会产生 ID = 148 的一行数据，因此 COUNT 会给出结果 1，而不是 0。