代码之家 › 专栏 › 技术社区 › Omar J. RamÃrez

获取MySQL中不同日期时间范围值的计数

count datetime sql mysql

Omar J. RamÃrez · 技术社区 · 2 年前

我的MySQL数据库中有两个表:

users_metrics
id | user_id  | login_datetime      | logout_datetime   |
--------------------------------------------------.-----|
1  |    1     |2022-05-04 00:25:16  |2022-05-04 00:30:16|
-----------------------------------------------.--------|
2  |    3     |2022-05-04 10:29:16  |2022-05-04 10:40:16|
--------------------------------------------------------|
3  |    1     |2022-05-04 11:48:16  |2022-05-04 11:49:56|
--------------------------------------------------------|
4  |    3     |2022-05-04 11:58:16  |2022-05-04 12:20:16|
--------------------------------------------------------|
5  |    1     |2022-05-04 16:28:16  |2022-05-04 17:29:56|
--------------------------------------------------------|

files_uploaded
id  | type   | file_upload_datetime | user_id |
-----------------------------------------------
1   |  csv   |2022-05-04 00:29:16   |   1     |
-----------------------------------------------
2   |  csv   |2022-05-04 10:39:16   |   3     |
-----------------------------------------------
3   |  txt   |2022-05-04 11:49:16   |   1     |
-----------------------------------------------
4   |  txt   |2022-05-04 11:59:16   |   3     |
-----------------------------------------------
5   |  csv   |2022-05-04 12:09:16   |   3     |
-----------------------------------------------
6   |  txt   |2022-05-04 12:19:16   |   3     |
-----------------------------------------------
7   |  txt   |2022-05-04 16:29:16   |   1     |
-----------------------------------------------
8   |  csv   |2022-05-04 16:39:16   |   1     |
-----------------------------------------------
9   |  txt   |2022-05-04 16:49:16   |   1     |
-----------------------------------------------
10  |  csv   |2022-05-04 17:29:16   |   1     |
-----------------------------------------------

这个 users_metrics 表保存某个用户的登录和注销时间以及 files_uploaded 表记录用户上传的记录。我正在寻找一个查询,它允许获取第一个表建立的datetime范围内上传文件的数量。这将是我想要的结果的一个视图。

id | user_id  | login_datetime      | logout_datetime   | total_files_uploaded
--------------------------------------------------.-----|---------------------|
1  |    1     |2022-05-04 00:25:16  |2022-05-04 00:30:16|        1      
-----------------------------------------------.--------|---------------------|
2  |    3     |2022-05-04 10:29:16  |2022-05-04 10:40:16|        1
--------------------------------------------------------|---------------------|
3  |    1     |2022-05-04 11:48:16  |2022-05-04 11:49:56|        1
--------------------------------------------------------|---------------------|
4  |    3     |2022-05-04 11:58:16  |2022-05-04 12:20:16|        3
--------------------------------------------------------|---------------------|
5  |    1     |2022-05-04 16:28:16  |2022-05-04 17:29:56|        3
--------------------------------------------------------|---------------------|

1 回复 | 直到 2 年前

flwd 2 年前

SELECT u.user_id,
       u.login_datetime,
       u.logout_datetime,
       coalesce(count(f.id), 0) total_files_uploaded
  FROM user_metrics u
  LEFT JOIN files_uploaded f
    ON u.user_id = f.user_id
   AND f.file_uploaded_datetime BETWEEN u.login_datetime AND u.logout_datetime
 GROUP BY u.user_id, u.login_datetime, u.logout_datetime;

输出:

用户id	登录日期时间	注销日期时间	上传的文件总数
1.	2022-05-04 00:25:16	2022-05-04 00:30:16	1.
3.	2022-05-04 10:29:16		1.
1.	2022-05-04 11:48:16	2022-05-04 11:49:56	1.
3.	2022-05-04 11:58:16	2022-05-04 12:20:16	3.
1.		2022-05-04 17:29:56	4.

如果用户在登录期间未上载任何文件,则显示0。如果不想为未上载任何文件的用户显示条目,可以用内部联接替换左侧联接,并删除 coalesce .