mysql - 需要帮助从表中获取自定义数据

Question

+----+----------+---------+----+------+-----+------+------+
| id |   date   |  time   | s  | name | act |  l   |  p   |
+----+----------+---------+----+------+-----+------+------+
|  1 | 04-11-19 | 05:05.0 | my | A    | b   |  100 |  105 |
|  2 | 04-11-19 | 06:06.0 | my | A    | s   |  100 |   97 |
|  3 | 04-11-19 | 03:02.0 | my | B    | s   |  201 |  196 |
|  4 | 03-11-19 | 03:02.0 | my | B    | b   |  202 |  209 |
|  5 | 03-11-19 | 03:02.0 | my | E    | b   | 1202 | 1209 |
|  6 | 03-11-19 | 23:32.0 | my | E    | s   | 1188 | 1180 |
|  7 | 02-11-19 | 19:08.0 | my | C    | b   |  272 |  280 |
|  8 | 04-11-19 | 32:28.0 | my | D    | s   |  356 |  356 |
|  9 | 02-11-19 | 11:09.0 | my | B    | s   |  204 |  198 |
| 10 | 04-11-19 | 21:11.0 | my | E    | b   | 1207 | 1212 |
+----+----------+---------+----+------+-----+------+------+

让上面提到的 MYSQL 表命名为 t。现在我想提取下面提到的数据。

1. 希望获取具有今天或最新日期的行的名称、行为、日期、时间，按日期 DESC 和时间 DESC 按名称分组 WHERE s = my.假设今天的日期为 04-11-19。结果表应如下所示。

+------+-----+----------+---------+
| name | act |   date   |  time   |
+------+-----+----------+---------+
| A    | s   | 04-11-19 | 06:06.0 |
| A    | b   | 04-11-19 | 05:05.0 |
| B    | s   | 04-11-19 | 03:02.0 |
| C    | b   | 02-11-19 | 19:08.0 |
| D    | s   | 04-11-19 | 32:28.0 |
| E    | b   | 04-11-19 | 21:11.0 |
+------+-----+----------+---------+

希望根据每组名称的日期和时间获取最近两个条目的名称、行为、日期和时间，其中 s = my.结果表应如下所示。

+------+-----+----------+---------+
| name | act |   date   |  time   |
+------+-----+----------+---------+
| A    | s   | 04-11-19 | 06:06.0 |
| A    | b   | 04-11-19 | 05:05.0 |
| B    | s   | 04-11-19 | 03:02.0 |
| B    | b   | 03-11-19 | 03:02.0 |
| C    | b   | 02-11-19 | 19:08.0 |
| D    | s   | 04-11-19 | 32:28.0 |
| E    | b   | 04-11-19 | 21:11.0 |
| E    | s   | 03-11-19 | 23:32.0 |
+------+-----+----------+---------+

我在下面编写了 SQL，它限制了总体输出，因为我想限制每组名称..

SELECT 名称、行为、日期、时间 FROM t WHERE s = 'my' ORDER BY name ASC, date DESC, time DESC LIMIT 2

我期望下表作为要求 1 的输出。

+------+-----+----------+---------+
| name | act |   date   |  time   |
+------+-----+----------+---------+
| A    | s   | 04-11-19 | 06:06.0 |
| A    | b   | 04-11-19 | 05:05.0 |
| B    | s   | 04-11-19 | 03:02.0 |
| C    | b   | 02-11-19 | 19:08.0 |
| D    | s   | 04-11-19 | 32:28.0 |
| E    | b   | 04-11-19 | 21:11.0 |
+------+-----+----------+---------+

或者

预计下表作为要求 2 的输出。

+------+-----+----------+---------+
| name | act |   date   |  time   |
+------+-----+----------+---------+
| A    | s   | 04-11-19 | 06:06.0 |
| A    | b   | 04-11-19 | 05:05.0 |
| B    | s   | 04-11-19 | 03:02.0 |
| B    | b   | 03-11-19 | 03:02.0 |
| C    | b   | 02-11-19 | 19:08.0 |
| D    | s   | 04-11-19 | 32:28.0 |
| E    | b   | 04-11-19 | 21:11.0 |
| E    | s   | 03-11-19 | 23:32.0 |
+------+-----+----------+---------+

我在论坛中看到过相关示例，但无法关联。请帮忙。

Answer 1

终于得到答案了。下面的代码将完成第二个问题的工作。

SELECT name, act, date, time FROM (SELECT *, RANK() OVER (PARTITION BY name ORDER BY date DESC, time DESC) AS rnk FROM t) AS x WHERE rnk <= 2 AND s = 'my'