MYSQL:仅选择最新记录(在左侧联接表上) [英] MYSQL: select latest record only (on left join table)

问题描述

我有2张桌子:

表1:

ID | Mobile Number | Name | Ordered Product| Order Date

表2:

ID(foreign_key can be inserted multipletimes in this table) |Contacted_for | Time(timestamp)

我需要查询以显示表1中的所有数据，如果表2中存在该ID，则需要显示该ID的表2上最后插入的记录(随时间变化)

I need a query to display all the data in Table1 and if the ID is present in Table 2, I need to display the last inserted record on Table2(with time) of that ID

我的查询是

select a.* , b.* FROM table1 a LEFT JOIN table2 b ON a.ID=b.ID GROUP BY a.ID ORDER BY b.Time DESC

在我的查询中，当我删除Group By a.ID时，它可以工作，但会显示所有结果.但我只想显示table2的最终记录(没有重复的ID记录)

Here in my query when I remove Group By a.ID, it works but shows all results. But I want to show final record of table2 only(no duplicate ID records)

预先感谢

推荐答案

为此，您将需要一些子查询:

You'll need some subquery's for that:

SELECT
    a.*, b.*
FROM
    table1 a
LEFT JOIN
    (SELECT c.id, d.contacted_for, c.time
     FROM
         (SELECT
            id,
            MAX(time) time
         FROM
             table2
         GROUP BY id
         ) c
     JOIN
         table2 d
         ON c.id = d.id AND d.time = c.time
     ) b
     ON a.id = b.id

这篇关于MYSQL:仅选择最新记录(在左侧联接表上)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！