訪問表
| 用戶身份 | 已訪問_IN |
|---|---|
| 518 | 2022-04-13 20:37:04 |
| 518 | 2021-12-29 22:26:50 |
| 518 | 2021-03-04 04:22:46 |
| 518 | 2021-08-13 02:14:54 |
| 518 | 2022-05-26 20:49:01 |
| 518 | 2022-05-05 17:47:46 |
| 518 | 2021-09-12 08:58:33 |
| 518 | 2021-04-07 18:36:59 |
| 518 | 2021-06-14 04:47:52 |
| 518 | 2021-12-26 22:16:47 |
假設當前時間是28-5-2022 15:00:00，我正在嘗試按日期過濾訪問。
執行命令時的預期結果
| LAST_60_MINUTES | LAST_24_HOURS | LAST_7_DAYS | LAST_30_DAYS | LAST_6_MONTHS | LAST_12_MONTHS |
|---|---|---|---|---|---|
| 0 | 0 | 1 | 2 | 5 | 8 |
這是我嘗試過的，但沒有按我的意愿作業
SELECT
COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE))) AS LAST_60_MINUTES,
COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR))) AS LAST_24_HOURS,
COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY))) AS LAST_7_DAYS,
COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY))) AS LAST_30_DAYS,
COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH))) AS LAST_6_MONTHS,
COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH))) AS LAST_12_MONTHS
FROM VISITS WHERE
USER_ID = 518
有關更多資訊，請查看此問題
uj5u.com熱心網友回復：
要么使用CASE運算式：
SELECT
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE) THEN 1 END) AS LAST_60_MINUTES,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) AS LAST_24_HOURS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) AS LAST_7_DAYS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) AS LAST_30_DAYS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 1 END) AS LAST_6_MONTHS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH) THEN 1 END) AS LAST_12_MONTHS
FROM VISITS
WHERE USER_ID = 518;
或SUM()代替COUNT()：
SELECT
SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE)) AS LAST_60_MINUTES,
SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) AS LAST_24_HOURS,
SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS LAST_7_DAYS,
SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS LAST_30_DAYS,
SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS LAST_6_MONTHS,
SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH)) AS LAST_12_MONTHS
FROM VISITS
WHERE USER_ID = 518;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482488.html