从一个表中选择行，以一对多关系将另一表中的最新行连接起来 [英] Select rows from one table, join most recent row from other table with one-to-many relationship
问题描述
我想做的是从一个表(表A)中选择一组特定的行，并与另一个表(表B)连接，这样，表A中只会出现一条记录，并与最近的记录连接来自表B，基于datetime列.
What I would like to do is select a specific set of rows from one table (table A) and join with another table (table B), such that only one record will appear from table A, joined with the most recent record from table B, based on a datetime column.
例如，表A具有以下结构(大大简化了):
For example, table A has this structure (heavily simplified):
id | col_1 | col_2
---+-----------+----------------
1 | something | something else
2 | val_1 | val_2
3 | stuff | ting
4 | goats | sheep
表B看起来像这样:
id | fk_A | datetime_col | col_3
---+-----------+---------------------+--------
1 | 1 | 2012-02-01 15:42:14 | Note 1
2 | 1 | 2012-02-02 09:46:54 | Note 2
3 | 1 | 2011-11-14 11:18:32 | Note 3
4 | 2 | 2009-04-30 16:49:01 | Note 4
5 | 4 | 2013-06-21 15:42:14 | Note 5
6 | 4 | 2011-02-01 18:44:24 | Note 6
我想要的是一个看起来像这样的结果集:
What I would like is a result set that looks like this:
id | col_1 | col_2 | datetime_col | col_3
---+-----------+----------------+---------------------+--------
1 | something | something else | 2012-02-02 09:46:54 | Note 2
2 | val_1 | val_2 | 2009-04-30 16:49:01 | Note 4
3 | stuff | ting | NULL | NULL
4 | goats | sheep | 2013-06-21 15:42:14 | Note 5
因此您可以看到表B已与B.fk_A = A.id上的表A联接在一起，但是结果中仅包括B中最新的对应记录.
So you can see that table B has been joined with table A on B.fk_A = A.id, but only the most recent corresponding record from B has been included in the results.
我尝试了SELECT DISTINCT，LEFT JOIN和子查询的各种组合，但是我无法使它正常工作，我要么没有结果，要么就得到了类似的东西:
I have tried various combinations of SELECT DISTINCT, LEFT JOIN and sub-queries and I just can't get it to work, I either get no results or something like this:
id | col_1 | col_2 | datetime_col | col_3
---+-----------+----------------+---------------------+--------
1 | something | something else | 2012-02-01 15:42:14 | Note 1
1 | something | something else | 2012-02-02 09:46:54 | Note 2
1 | something | something else | 2011-11-14 11:18:32 | Note 3
2 | val_1 | val_2 | 2009-04-30 16:49:01 | Note 4
3 | stuff | ting | NULL | NULL
4 | goats | sheep | 2013-06-21 15:42:14 | Note 5
4 | goats | sheep | 2011-02-01 18:44:24 | Note 6
...重复表A中的记录.
...with the records from table A repeated.
很明显，我的SQL-fu不足以完成此任务，因此，如果您当中有人可以向我指出正确的方向，我将不胜感激.我已经在Google上进行了大量的Google搜索和搜索工作，但没有找到与该特定任务相匹配的任何内容，尽管我确定之前曾有人问过这个问题-我怀疑有一个我遗忘/未意识到的SQL关键字，并且如果我进行搜索，我会立即找到答案.
Obviously my SQL-fu is just not good enough for this task, so I would be most grateful if one of you kind people could point me in the right direction. I have done quite a bit of Googling and searching around SO and I have not found anything that matches this specific task, although I am sure the question has been asked before - I suspect there is an SQL keyword that I am forgetting/unaware of and if I searched for that I would find the answer instantly.
我认为此问题会解决相同的问题，尽管我不确定100％并且接受的答案涉及SELECT TOP，我以为(?)在MySQL中无效.
I think this question deals with the same problem although I am not 100% sure and the accepted answer involves SELECT TOP, which I thought (?) was not valid in MySQL.
由于我的实际查询要复杂得多，并且连接了多个表，因此我将在对其进行任何更改的情况下进行显示:
As my actual query is much more complicated and joins several tables, I shall show it in case it makes any difference to how this is done:
SELECT `l` . * , `u`.`name` AS 'owner_name', `s`.`name` AS 'acquired_by_name', `d`.`type` AS `dtype` , `p`.`type` AS `ptype`
FROM `leads` l
LEFT JOIN `web_users` u ON `u`.`id` = `l`.`owner`
LEFT JOIN `web_users` s ON `s`.`id` = `l`.`acquired_by`
LEFT JOIN `deal_types` d ON `d`.`id` = `l`.`deal_type`
LEFT JOIN `property_types` p ON `p`.`id` = `l`.`property_type`
此查询有效，并返回我想要的数据(有时我还添加了WHERE子句，但是效果很好)，但是我现在想:
This query works and returns the data I want (sometimes I also add a WHERE clause but this works fine), but I would now like to:
LEFT JOIN `notes` n ON `n`.`lead_id` = `l`.`id`
......，其中notes包含许多记录"，而leads包含与它们相关的一个记录".
...where notes contains the "many records" and leads contains the "one record" they relate to.
还应该注意，潜在地，我也想返回最旧的记录(在不同的查询中)，但是我认为这是将ASC/DESC转换到某个位置或类似的简单操作的简单情况.
It should also be noted that potentially I would also want to return the oldest record (in a different query) but I imagine this will be a simple case of inverting an ASC/DESC somewhere, or something similarly easy.
推荐答案
我认为这对您有帮助:
SELECT A.id, A.col_1, A.col_2, A.datetime_col, A.col_3
FROM
(SELECT B.id, B.col_1, B.col_2, C.datetime_col, C.col_3
FROM tableA B LEFT OUTER JOIN tableB C ON B.id = C.id
ORDER BY C.datetime_col desc) as A
GROUP BY A.id
这篇关于从一个表中选择行，以一对多关系将另一表中的最新行连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
