mysql - MySQL 子查询从当前查询中的用户 ID 获取不同表中的用户名。如何?
问题描述
以下查询是迄今为止我能学到的最复杂的查询。它从 tbl_user_time_log 计算登录持续时间。
SELECT USER_ID,
UTL_DTSTAMP,
UTL_TASK,
SUM(UTL_DURATION) AS UTL_DURATION_TOTAL
FROM (
SELECT A.PK_USER_TIME_LOG_ID,
A.CLIENT_ID,
A.PROJECT_ID,
A.USER_ID,
A.UTL_DTSTAMP,
/* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
A.UTL_LATITUDE,
A.UTL_LONGITUDE,
A.UTL_EVENT,
A.UTL_TASK,
/* DURATION in seconds */
TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM tbl_user_time_log A
INNER JOIN tbl_user_time_log B
ON B.PK_USER_TIME_LOG_ID = ( A.PK_USER_TIME_LOG_ID + 1 )
WHERE A.USER_ID = '465615'
/* Between current pay period Start date and Current pay period end date */
/* First day of the week is Monday.*/
AND ( A.UTL_DTSTAMP BETWEEN '2019-02-24' AND '2019-03-04' )
/* Filter out Clock Out. */
AND A.UTL_EVENT <> 'CLOCK OUT'
/* Our derived table... */
) AS tbl_derived_1
GROUP BY USER_ID, UTL_TASK;
...返回:
如何使用相同的查询,但对于它选择的每个用户 ID ,从tbl_user显示相应的用户名和用户姓?
tbl_user
解决方案
假设您有一个表用户,其中包含通过 id 与 tbl_user_time_log 相关的名字和姓氏,您可以使用连接
SELECT USER_ID,
u.firstname,
u.lastname,
UTL_DTSTAMP,
UTL_TASK,
SUM(UTL_DURATION) AS UTL_DURATION_TOTAL
FROM (
SELECT A.PK_USER_TIME_LOG_ID,
A.CLIENT_ID,
A.PROJECT_ID,
A.USER_ID,
A.UTL_DTSTAMP,
/* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
A.UTL_LATITUDE,
A.UTL_LONGITUDE,
A.UTL_EVENT,
A.UTL_TASK,
/* DURATION in seconds */
TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM tbl_user_time_log A
INNER JOIN tbl_user_time_log B
ON B.PK_USER_TIME_LOG_ID = ( A.PK_USER_TIME_LOG_ID + 1 )
WHERE A.USER_ID = '465615'
/* Between current pay period Start date and Current pay period end date */
/* First day of the week is Monday.*/
AND ( A.UTL_DTSTAMP BETWEEN '2019-02-24' AND '2019-03-04' )
/* Filter out Clock Out. */
AND A.UTL_EVENT <> 'CLOCK OUT'
/* Our derived table... */
) AS tbl_derived_1
INNER JOIN user u ON u.id = tbl_derived_1.USER_ID
GROUP BY USER_ID, UTL_TASK;
推荐阅读
- python - Python 计算 gotos 和 USE_COMPUTED_GOTOS?
- web-scraping - Scraping Data on TradingView with BeautifulSoup
- html - How can I horizontally center grid items that have been wrapped onto a new row
- php - Creating zipped file of many pdfs automatically
- android - snapHelper.findSnapView() 总是返回 null
- bash - 将第二个参数传递给 Bash 函数
- image-processing - 如何提高训练和测试的准确性
- c++ - std::destroy 是基本类型的无操作吗?
- linq - linq 组消息仅显示最后的消息,就像应用程序所做的那样
- node.js - 尝试连接 Express 服务器以向 PostgreSQL 数据库发出 POST/GET 请求