从多个表中选择最新行 | 从多个表中选择最新行

本文介绍了从多个表中选择最新行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有多个表的查询，得到以下结果.然后我想按t.id分组.我知道我可以简单地按t.id使用group，但是如何选择t.id的最新行，即url tw5.jpg和created_time 10000004

I have this query with multiple tables and I got the following result. Then I want to group by t.id. I know I can simply use group by t.id but how can I select the latest row of t.id which is url tw5.jpg and created_time 10000004

SELECT p.url,t.name,t.num_photo,t.id
FROM photos AS p
LEFT JOIN tag_maps AS tm ON p.id = tm.photo_id
LEFT JOIN tags AS t ON t.id = tm.tag_id

url                       created_time    name      num_photo  id
assets/img/tags/tw1.jpg   1000001         my house  1           1
assets/img/tags/tw2.jpg   1000002         dog       1           2
assets/img/tags/tw3.jpg   1000003         taiwan    2           3
assets/img/tags/tw5.jpg   1000004         taiwan    2           3

这是我使用t.id分组时得到的

This is what i get when using group by t.id

url                       created_time    name      num_photo  id
assets/img/tags/tw1.jpg   1000001         my house  1           1
assets/img/tags/tw2.jpg   1000002         dog       1           2
assets/img/tags/tw3.jpg   1000003         taiwan    2           3

这就是我想要的

url                       created_time    name      num_photo  id
assets/img/tags/tw1.jpg   1000001         my house  1           1
assets/img/tags/tw2.jpg   1000002         dog       1           2
assets/img/tags/tw5.jpg   1000004         taiwan    2           3

已编辑

照片表

id    url                      created_time
1     assets/img/tags/tw1.jpg  1000001
2     assets/img/tags/tw2.jpg  1000002
3     assets/img/tags/tw3.jpg  1000003
4     assets/img/tags/tw5.jpg  1000004

标签表

id name     num_photo
1  my house 1
2  dog      1
3  taiwan   2

Tag_maps

id   tag_id   photo_id
1    1        1
2    2        2
3    3        3
4    3        4

推荐答案

编辑#2:在看不到每个表或表结构的示例数据的情况下，我猜想下面的方法会起作用:

Edit #2: Without seeing sample data from each table or the table structures, I am guessing that the following will work:

SELECT p.url,
  created_time,
  t.name,
  t.num_photo,
  t.id
FROM photos p
LEFT JOIN tag_maps AS tm
    ON p.id = tm.photo_id
LEFT JOIN tags AS t
    ON t.id = tm.tag_id
INNER JOIN
(
  select max(created_time) MaxDate, t.id
  FROM photos p
  LEFT JOIN tag_maps AS tm
      ON p.id = tm.photo_id
  LEFT JOIN tags AS t
      ON t.id = tm.tag_id
  group by t.id
) d
  on p.created_time = d.MaxDate
  and t.id = d.id;

请参见带有演示的SQL提琴

另一种写此方法的方法是使用子查询，该子查询从tag_maps表中返回max(photo_id)由tag_Id并在联接中使用该结果:

Or another way to write this is with a subquery that returns the max(photo_id) by tag_Id from the tag_maps table and use that result in the join:

SELECT p.url,
  created_time,
  t.name,
  t.num_photo,
  t.id
FROM photos p
INNER JOIN
(
  select max(photo_id) photo_id, tag_id
  from tag_maps
  group by tag_id
) AS tm
    ON p.id = tm.photo_id
LEFT JOIN tags AS t
    ON t.id = tm.tag_id

请参见带有演示的SQL提琴

结果是:

|                     URL | CREATED_TIME |     NAME | NUM_PHOTO | ID |
----------------------------------------------------------------------
| assets/img/tags/tw1.jpg |      1000001 | my house |         1 |  1 |
| assets/img/tags/tw2.jpg |      1000002 |      dog |         1 |  2 |
| assets/img/tags/tw5.jpg |      1000004 |   taiwan |         2 |  3 |

编辑#1，因为您的网址不同，并且您需要max(id)，那么您应该可以使用:

这篇关于从多个表中选择最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！