MySQL 函数 IFNULL 不能与 GROUP BY 一起使用

Question

我有一个包含用户列表的标准表，并且有一个包含 UNIX 时间戳(显示用户登录时间)的 lastactivity 列和 timestamp 列code> 带有 UNIX 时间戳，显示它们何时注册。

我构建了一个 SQL 查询，显示从现在起 24 小时(86400 秒)内有多少用户处于事件状态，并按周对结果进行分组，以便计数器计算每周注册的用户数量:

SELECT
    IFNULL(COUNT(*),0) as `counter`,
    (WEEK(`timestamp`)) as `week`
FROM
    `clients`
WHERE
    (CAST(UNIX_TIMESTAMP() as signed) - CAST(`lastactivity` as signed)) <= 86400
GROUP BY
    WEEK(`timestamp`);

问题是函数 IFNULL(COUNT(*),0) 没有按我的预期工作。如果计数器上有 NULL/0，即使使用 IFNULL() MySQL 函数，此 SQL 查询也不会显示星期。这可能是因为 GROUP BY 的工作原理。例如我会得到这样的结果:

counter | week
   2    |  11
   1    |  13
   9    |  14
   6    |  17

但我想每周显示如下:

counter | week
   2    |  11
   0    |  12
   1    |  13
   9    |  14
   0    |  15
   0    |  16
   6    |  17

有人知道如何解决这个问题吗？

Gordon 试图通过获取 LEFT JOIN 查询来帮助我，但我仍然得到相同的结果，也许我在这里做错了什么:

SELECT
    COUNT(a.id) as `counter`,
    (WEEK(b.timestamp)) as `week`
FROM
    `users` a
LEFT JOIN
    `users` b
ON
    a.id = b.id
WHERE
    (CAST(UNIX_TIMESTAMP() as signed) - CAST(a.lastactivity as signed)) <= 86400
GROUP BY
    WEEK(b.timestamp);

Answer 1

问题是您不了解查询是如何工作的。 IFNULL()(或标准版本 COALESCE() 将 NULL 的列值转换为其他值。但是，COUNT () 永远不会返回 NULL。因此，将其省略:

SELECT COUNT(*) as `counter`, WEEK(`timestamp`) as `week`
FROM `clients`
WHERE (CAST(UNIX_TIMESTAMP() as signed) - CAST(`lastactivity` as signed)) <= 86400
GROUP BY WEEK(`timestamp`);

您的问题是缺少行，而不是 NULL 值。您必须使用LEFT JOIN来解决这个问题。

编辑:

您需要一个左连接来包含所有星期:

SELECT COUNT(c.timestamp) as `counter`, wk as `week`
FROM (SELECT 11 as wk UNION ALL
      SELECT 12 UNION ALL
      SELECT 13 UNION ALL
      SELECT 14 UNION ALL
      SELECT 15 UNION ALL
      SELECT 16 UNION ALL
      SELECT 17
     ) w LEFT JOIN
     `clients` c
     ON WEEK(c.`timestamp`) = w.wk
WHERE (CAST(UNIX_TIMESTAMP() as signed) - CAST(`lastactivity` as signed)) <= 86400
GROUP BY WEEK(`timestamp`);