我有一个名为-'users'的示例sql表，其中包含以下记录：
我想获得每一个用户的计数在每日，每周和每月的基础上与预期的outlike为：
您可以使用如下所示的查询：
SELECT user_id
, SUM(IF( transaction_time > CURRENT_DATE() - INTERVAL 1 DAY, 1, 0)) as daily_count
, SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 WEEK, 1, 0)) as weekly_count
, SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 MONTH, 1, 0)) as monthly_count
FROM yourTable
GROUP BY user_ID
ORDER by user_id;
样品
MariaDB [bernd]> select * from yourTable;
+----+---------+---------------------+-------+
| id | user_id | transaction_time | price |
+----+---------+---------------------+-------+
| 1 | 1 | 2021-04-22 10:00:00 | 20 |
| 2 | 2 | 2021-04-03 20:00:00 | 10 |
| 3 | 2 | 2021-04-04 20:00:00 | 11 |
| 4 | 3 | 2021-04-05 20:00:00 | 12 |
| 5 | 1 | 2021-03-03 20:00:00 | 10 |
| 6 | 3 | 2021-01-06 20:00:00 | 9 |
+----+---------+---------------------+-------+
6 rows in set (0.01 sec)
MariaDB [bernd]> SELECT user_id
-> , SUM(IF( transaction_time > CURRENT_DATE() - INTERVAL 1 DAY, 1, 0)) as daily_count
-> , SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 WEEK, 1, 0)) as weekly_count
-> , SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 MONTH, 1, 0)) as monthly_count
-> FROM yourTable
-> GROUP BY user_ID
-> ORDER by user_id;
+---------+-------------+--------------+---------------+
| user_id | daily_count | weekly_count | monthly_count |
+---------+-------------+--------------+---------------+
| 1 | 1 | 1 | 1 |
| 2 | 0 | 0 | 2 |
| 3 | 0 | 0 | 1 |
+---------+-------------+--------------+---------------+
3 rows in set (0.00 sec)
MariaDB [bernd]>
