按日期选择最后7天的记录，然后避免使用特定列的重复

发布于 2025-01-19 05:23:03 字数 2096 浏览 0 评论 0 原文

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

我的SQL中有下表：

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

我想显示最后7天的记录，但按最新日期订购，并且仅显示每本电子邮件的最新记录，就像这样

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

我尝试了此查询来实现这一目标：

尝试1：选择 *从表中访问（现在（） - 间隔7天）和（通过电子邮件订单）访问desc

尝试2：从表中访问（现在访问）（现在）（） - 间隔7天）和NOW（）订单访问desc

结果正确显示，但以奇怪的顺序显示。如果我按子句放弃小组，它将正确显示，但还包括电子邮件列的重复项。

原文

I'm pretty sure the problem I'm facing is quite small, but I can't figure out what's wrong.

I have the following table in my sql:

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

I want to display the last 7 days records but ordered by newest date and showing only the newest record by each email, like this

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

I've tried this query to achieve this:

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

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

The results are displayed correctly, but in a strange order.
If I give up GROUP BY clause, it displays correctly but also includes EMAIL column duplicates.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟凡古楼 2025-01-26 05:23:03

尝试将 MAX 与 group by 一起使用

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

try using MAX with group by

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

回复收藏 0 原文

清风夜微凉 2025-01-26 05:23:03

我向您建议此代码：‌

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

I suggest this code to you: ‌

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

回复收藏 0 原文

染墨丶若流云 2025-01-26 05:23:03

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

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

You can add a subquery to number the rows within each group, then select from this subquery only the first rows in each group:

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

回复收藏 0 原文

~没有更多了~