mysql Cross table join grouping not using FK to group [duplicate]

n1bvdmb6 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(136)

This question already has answers here:

SQL select only rows with max value on a column [duplicate] (27 answers)
Selecting from two tables with inner join and limit [duplicate] (5 answers)
Closed yesterday.
The community is reviewing whether to reopen this question as of yesterday.
Hi I have 2 tables Entity and Feedback
Lets say that table Feedback has following columns.

id (uuid) 
created_at (datetime) 
entity_id (uuid, FK to entity table)
...

And table Entity has following columns.

id (uuid) 
project_id (uuid)
...

What I need to do is find latest feedback for each entity's project id.
So far I got this:

SELECT f.id, f.created_at, e.id, e.project_id from entity e
RIGHT JOIN feedback f on e.id = f.entity_id

Which gets me list of all entities with feedback, but somehow I need to group it by project_id and get latest feedbackId for that group
http://www.sqlfiddle.com/#!9/fda5cf/1
Edit: Question marked as duplicated by: Selecting from two tables with inner join and limit I think that in this case that solution can not be applied. Reason is that I do not use grouped value as FK between tables, but additional value (project_id) which is contained only in entity table
Edit 2: Added sqlFiddle

mysql

来源：https://stackoverflow.com/questions/74861331/cross-table-join-grouping-not-using-fk-to-group

1条答案

按热度按时间

oxf4rvwz1#

On MySQL 8+ we can use ROW_NUMBER here:

WITH cte AS (
    SELECT f.id AS f_id, f.created_at, e.id, e.project_id,
           ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY f.created_at DESC) rn
    FROM Entity e
    LEFT JOIN Feedback f ON f.entity_id = e.id
)

SELECT id, f_id, created_at, project_id
FROM cte
WHERE rn = 1;

On earlier versions of MySQL, we can use a join approach:

SELECT f.id AS f_id, f.created_at, e.id, e.project_id
FROM Entity e
LEFT JOIN Feedback f ON f.entity_id = e.id
INNER JOIN
(
    SELECT entity_id, MAX(created_at) AS max_created_at
    FROM Feedback
    GROUP BY entity_id
) t
    ON t.entity_id = f.entity_id AND
       t.max_created_at = f.created_at;

赞(0）回复(0）举报 2022-12-22

我来回答

mysql Cross table join grouping not using FK to group [duplicate]

1条答案

相关问题

热门标签

最新问答