mysql - 基于 ORDER 字段而不是首次出现的累积字段

Question

我目前有这个事件表:

id | user | country | when
---+------+---------+-----------------------
5  | 1    | my      | "2011-07-21 00:00:00"
19 | 1    | my      | "2014-04-02 18:00:00"
20 | 1    | my      | "2014-04-09 18:00:00"
29 | 1    | my      | "2011-09-27 15:00:00"
30 | 1    | my      | "2011-02-01 15:00:00"
31 | 1    | my      | "2012-05-12 19:00:00"
75 | 1    | my      | "2014-06-20 16:00:00"

在输出中，我需要获取用户出现国家/地区的次数以及累积的次数。为此，我使用以下查询:

SELECT f.id, f.user, f.country, f.`when`, (
  SELECT COUNT(*) FROM events AS f2
  WHERE f2.country = f.country AND f2.user = f.user
) AS countrytimes, (
  SELECT count(*) FROM events AS f3
  WHERE f3.country = f.country AND f3.id <= f.id AND f3.user = f.user
  ORDER BY f.`when` DESC
) AS accumulated
FROM events AS f
WHERE f.user = 1
AND f.country = 'my'
ORDER BY f.`when` DESC

该查询的输出基于字段when，直到这里为止。问题在于，字段累积是基于所请求的用户和国家/地区的首次出现，而不是基于when字段。

id | user | country | when                  | countrytimes | accumulated
---+------+---------+-----------------------+--------------+-------------
75 | 1    | my      | "2014-06-20 16:00:00" | 7            | 7
20 | 1    | my      | "2014-04-09 18:00:00" | 7            | 3
19 | 1    | my      | "2014-04-02 18:00:00" | 7            | 2
31 | 1    | my      | "2012-05-12 19:00:00" | 7            | 6
29 | 1    | my      | "2011-09-27 15:00:00" | 7            | 4
5  | 1    | my      | "2011-07-21 00:00:00" | 7            | 1
30 | 1    | my      | "2011-02-01 15:00:00" | 7            | 5

表和查询在此 SQLFiddle 。

如何对其进行排序，以便 when = 2011 年 2 月 1 日的行获得 accumulated = 1？

Answer 1

您只需将累积标准从 f3.id <= f.id 更改即可。至f3.when <= f.when按日期而不是id获取累积值；

SELECT f.id, f.user, f.country, f.when, (
  SELECT COUNT(*) FROM events AS f2
  WHERE f2.country = f.country AND f2.user = f.user
) AS countrytimes, (
  SELECT count(*) FROM events AS f3
  WHERE f3.country = f.country AND f3.when <= f.when AND f3.user = f.user
  ORDER BY f.`when` DESC
) AS accumulated
FROM events AS f
WHERE f.user = 1
AND f.country = 'my'
ORDER BY f.`when` DESC

An SQLfiddle to test with .