我有两张桌子
表A有usr_id , usr_name , usr_status [1 or 0]
表B有id, usr_id,tracked_date [Each user can track multiple time]
需要获得每个用户拥有usr_status = 1的最后一条轨道
如何通过MySQL查询实现?
试着像:
SELECT
*
FROM
tableb b
LEFT JOIN
tablea a ON a.usr_id = b.usr_id
GROUP BY a.usr_id请给我建议?
发布于 2018-11-12 13:42:02
tracked_date获取usr_id的最大值尝试:
SELECT
ta.*, tb.*
FROM
tablea AS ta
JOIN tableb AS tb
ON tb.user_id = ta.usr_id
JOIN (
SELECT
user_id,
MAX(tracked_date) AS max_tracked_date
FROM tableb
GROUP BY user_id
) AS dt
ON dt.usr_id = tb.usr_id AND
dt.max_tracked_date = tb.tracked_date
WHERE ta.usr_status = 1在MySQL版本(8.0.2和更高版本)中,我们可以使用窗口函数。使用Row_Number(),我们可以将tracked_date值最高的行的行号设置为1,在usr_id的分区中。现在,我们可以将其用作派生表,并且只考虑行号等于1的行。
SELECT
dt.*
FROM
(
SELECT
ta.*,
tb.*,
ROW_NUMBER() OVER (PARTITION BY ta.usr_id
ORDER BY tb.tracked_date DESC) AS row_no
FROM
tablea AS ta
JOIN tableb AS tb ON tb.usr_id = ta.usr_id
WHERE ta.usr_status = 1
) AS dt
WHERE dt.row_no = 1附加:如果您希望获取所有用户(status = 1),但还没有tracked_date条目,则使用直接LEFT JOIN将无法在表和派生表之间工作。
我们必须首先在单独的派生表中获得与最大表对应的完整行;然后从users表中执行LEFT JOIN,以获取所有用户。
SELECT
ta.*, dt2.*
FROM
tablea AS ta
LEFT JOIN
(SELECT tb.*,
dt.max_tracked_date
FROM tableb AS tb
JOIN (
SELECT
usr_id,
MAX(tracked_date) AS max_tracked_date
FROM tableb
GROUP BY usr_id
) AS dt
ON (dt.usr_id = tb.usr_id AND
dt.max_tracked_date = tb.tracked_date)
) AS dt2 ON ta.usr_id = dt2.usr_id
WHERE ta.usr_status = 1结果
| usr_id | usr_name | usr_status | id | usr_id | stage | tracked_date | max_tracked_date |
| ------ | -------- | ---------- | --- | ------ | ----- | ------------------- | ------------------- |
| 1 | john | 1 | 2 | 1 | 2 | 2018-11-12 13:12:12 | 2018-11-12 13:12:12 |
| 2 | dave | 1 | 3 | 2 | 1 | 2018-11-12 13:12:12 | 2018-11-12 13:12:12 |
| 3 | smith | 1 | | | | | |https://stackoverflow.com/questions/53263367
复制相似问题