我的 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表記錄了用戶上傳的檔案記錄。我正在尋找一個查詢，該查詢允許在第一個表建立的日期時間范圍內獲取上傳檔案的計數。這將是我正在尋找的結果的視圖。
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
--------------------------------------------------------|---------------------|
uj5u.com熱心網友回復：
您可以根據用戶 id 和登錄時間加入表，然后計算上傳：
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;
輸出：
| 用戶身份 | 登錄日期時間 | 注銷日期時間 | total_files_uploaded |
|---|---|---|---|
| 1 | 2022-05-04 00:25:16 | 2022-05-04 00:30:16 | 1 |
| 3 | 2022-05-04 10:29:16 | 2022-05-04 10:40: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 16:28:16 | 2022-05-04 17:29:56 | 4 |
如果用戶在登錄期間沒有上傳任何檔案，這將顯示 0。如果您不想為沒有上傳任何檔案的用戶顯示條目，您可以將左連接替換為內連接并洗掉coalesce.
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472625.html