我很確定我面臨的問題很小，但我不知道出了什么問題。
我的 sql 中有下表：
----- ------------------ ---------------------
| ID | EMAIL | VISIT |
----- ------------------ ---------------------
| 1 | john@email.com | 2021-04-01,13:20:23 |
| 2 | peter@email.com | 2021-04-03,12:03:44 |
| 3 | daniel@email.com | 2021-04-04,13:21:12 |
| 4 | john@email.com | 2021-04-06,09:34:31 |
| 5 | peter@email.com | 2021-04-07,11:20:22 |
----- ------------------ ---------------------
我想顯示最近 7 天的記錄，但按最新日期排序，每封電子郵件僅顯示最新記錄，如下所示
----- ------------------ ---------------------
| ID | EMAIL | VISIT |
----- ------------------ ---------------------
| 3 | daniel@email.com | 2021-04-04,13:21:12 |
| 4 | john@email.com | 2021-04-06,09:34:31 |
| 5 | peter@email.com | 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 子句，它會正確顯示，但也包括 EMAIL 列重復項。
uj5u.com熱心網友回復：
嘗試使用 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
uj5u.com熱心網友回復：
我向您推薦此代碼：?
SELECT
EMAIL
FROM `table_name`
WHERE
VISIT BETWEEN(NOW() - INTERVAL 7 DAY) AND NOW()
GROUP BY
EMAIL
ORDER BY
MAX(VISIT)
DESC;
uj5u.com熱心網友回復：
您可以添加一個子查詢來對每個組中的行進行編號，然后從此子查詢中僅選擇每個組中的第一行：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/456365.html
下一篇：如何僅將日期列分為兩列