按日期选择最近7天的记录，并按特定列避免重复

Question

我很确定我面临的问题很小，但我不知道出了什么问题。

我的sql中有下表：

+-----+------------------+---------------------+
| ID  | EMAIL            | VISIT               |
+-----+------------------+---------------------+
| 1   | [e-mail]   | 2021-04-01,13:20:23 |
| 2   | [e-mail]  | 2021-04-03,12:03:44 |
| 3   | [e-mail] | 2021-04-04,13:21:12 |
| 4   | [e-mail]   | 2021-04-06,09:34:31 |
| 5   | [e-mail]  | 2021-04-07,11:20:22 |
+-----+------------------+---------------------+

我想显示最近7天的记录，但按最新日期排序，并在每个电子邮件中只显示最新记录，就像这个

+-----+------------------+---------------------+
| ID  | EMAIL            | VISIT               |
+-----+------------------+---------------------+
| 3   | [e-mail] | 2021-04-04,13:21:12 |
| 4   | [e-mail]   | 2021-04-06,09:34:31 |
| 5   | [e-mail]  | 2021-04-07,11:20:22 |
+-----+------------------+---------------------+

我尝试过这个查询来实现这一点：

尝试1:SELECT * FROM table WHERE VISIT BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW() GROUP BY EMAIL ORDER BY VISIT DESC

尝试2:SELECT DISTINCT (EMAIL) FROM table WHERE VISIT BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW() ORDER BY VISIT DESC

结果显示正确，但顺序很奇怪。如果我放弃GROUP BY子句，它将正确显示，但也包括重复的电子邮件列。

Answer 1

尝试通过对组使用MAX

SELECT EMAIL,MAX(VISIT)  as last_visit FROM table WHERE VISIT BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()  GROUP BY EMAIL ORDER BY last_visit DESC

Answer 2

我建议您使用以下代码：‌

SELECT
EMAIL
FROM `table_name`
WHERE
VISIT BETWEEN(NOW() - INTERVAL 7 DAY) AND NOW()
GROUP BY
EMAIL
ORDER BY
MAX(VISIT)
DESC;

Answer 3

您可以添加一个子查询来对每组中的行进行编号，然后从该子查询中只选择每组中的第一行：

SELECT EMAIL, VISIT FROM (
SELECT EMAIL, VISIT, ROW_NUMBER() OVER (PARTITION BY EMAIL ORDER BY VISIT DESC) AS 'RowNumber' 
FROM table 
WHERE VISIT>DATE_SUB(NOW(), INTERVAL 7 DAY)
) T1
WHERE RowNumber=1