mysql - ifnull 不适用于 mysql

Question

我有下表:

+----+------------+----------+------------------+| id | created_at | platform | platform_version |+----+------------+----------+------------------+|  1 |          1 | mac      | 1                ||  2 |          2 | mac      | 1                ||  3 |          3 | mac      | 2                ||  4 |          4 | mac      | 2                ||  5 |          5 | mac      | 2                ||  6 |          5 | mac      | 3                ||  7 |          2 | windows  | 1                ||  8 |          2 | windows  | 2                ||  9 |          3 | windows  | 3                || 10 |          3 | windows  | 1                || 11 |          4 | windows  | 2                || 12 |          4 | windows  | 3                || 13 |          5 | windows  | 4                || 14 |          5 | windows  | 1                || 15 |          6 | windows  | 2                || 16 |          6 | windows  | 3                |+----+------------+----------+------------------+

我想得到如下结果:

+-------------+---------------+---------------+------------+| group_count | running_total | windows_total | created_at |+-------------+---------------+---------------+------------+|           1 |             1 |             0 |          1 ||           6 |             7 |             4 |          2 ||           7 |            14 |             8 |          4 ||           2 |            16 |            10 |          6 |+-------------+---------------+---------------+------------+

但是当我执行这个选择语句时:

SELECT group_count,       (@r := @r + group_count ) AS running_total,       (@w := @w + ifnull( win_count, 0 )) AS windows_total,       t1.created_atFROM  (SELECT   (@r :=0),                COUNT( * ) AS group_count,                platform,                created_at       FROM     devices       GROUP BY created_at DIV 2      ) AS t1 LEFT JOIN      (SELECT   COUNT( * ) AS win_count,                created_at       FROM     devices       WHERE    platform = 'windows'       GROUP BY created_at DIV 2      ) AS t3 ON t1.created_at = t3.created_at

结果是这样的:

+-------------+---------------+---------------+------------+| group_count | running_total | windows_total | created_at |+-------------+---------------+---------------+------------+|           1 |             1 |          NULL |          1 ||           6 |             7 |          NULL |          2 ||           7 |            14 |          NULL |          4 ||           2 |            16 |          NULL |          6 |+-------------+---------------+---------------+------------+

合并也不起作用。有人能帮忙吗？谢谢。

Answer 1

作为记录，我引用@eggyal 的回答:

@w 最初是 NULL，因此将它添加到 IFNULL 的结果(无论结果是什么)都会产生 NULL。
在子选择中给它一个初始值，就像你对@r所做的那样

SELECT group_count,
       (@r := @r + group_count ) AS running_total,
       (@w := @w + ifnull( win_count, 0 )) AS windows_total,
       t1.created_at
FROM  (SELECT   (@r:= 0),(@w:= 0),
                COUNT(*) AS group_count,
                platform,
                created_at
       FROM     devices
       GROUP BY created_at DIV 2
      ) AS t1 LEFT JOIN
      (SELECT   COUNT( * ) AS win_count,
                created_at
       FROM     devices
       WHERE    platform = 'windows'
       GROUP BY created_at DIV 2
      ) AS t3 ON t1.created_at = t3.created_at

这应该可以解决问题。