联接2个表并仅显示id的最大值-mysql [英] join 2 tables and only display the max values for a id - mysql

问题描述

我有两个表，其中包含有关我在查询中加入的图形的信息.第一个表格包含工程图的唯一编号，标题和绘制人.第二个表包含修订版本和工程图的修订日期.

I have two tables that hold information about a drawing that I join in my query. The first table contains the drawings unique number, title and who it was drawn by. The second table contains the revision and the date the drawing was revised.

表1

|dwg_id|project_no|sws_dwg_no|dwg_title|dwg_by|
|1     |153       |153-100   |Pipe...  |JS    |

表2

|dwg_id|dwg_rev|dwg_date            |rev_id|
|1     |A      |2015-07-15 11:00:00 |1     |
|1     |B      |2015-07-23 12:00:00 |2     |
|1     |C      |2015-08-06 10:00:00 |3     |

我想加入两个表，并且只显示工程图的最新修订版本.

I want join the two tables and only show the most recent revision change for a drawing.

这是我当前的查询.

SELECT 
    `drawings`.`dwg_id`, 
    `project_no`, 
    `sws_dwg_no`, 
    `client_dwg_no`, 
    `dwg_title`, 
    `dwg_by`, 
    `dwg_rev`.`dwg_rev`, 
    `dwg_rev`.`dwg_date`, 
    MAX(`dwg_rev`.`dwg_rev`) AS dwg_rev 
FROM 
    (`drawings`) 
    JOIN `dwg_rev` ON `drawings`.`dwg_id` = `dwg_rev`.`dwg_id` 
WHERE 
    `project_no` = '153' 
GROUP BY 
    `sws_dwg_no`, 
    `dwg_rev`.`dwg_rev` 
ORDER BY 
    `dwg_rev`.`dwg_date` ASC, 
    `dwg_rev`.`dwg_rev` ASC

此查询返回的结果不包含最新的修订号，或返回每个图形的所有修订.

The results that this query returns doesn't contain the latest revision numbers or it returns all the revision for each drawing.

推荐答案

您可以使用以下查询:

SELECT d.*, dr.*
FROM drawings AS d
INNER JOIN (
   SELECT dwg_id, MAX(rev_id) AS maxRevId
   FROM dwg_rev
   GROUP BY dwg_id
) AS t ON d.dwg_id = t.dwg_id   
INNER JOIN dwg_rev AS dr ON t.dwg_id = dr.dwg_id AND t.maxRevId = dr.rev_id
WHERE project_no = 153

上面查询中的关键点是派生表的使用情况，该派生表按每个dwg_id返回最新修订版，即MAX(rev_id).在该派生表上使用INNER JOIN，您可以从dwg_rev表中准确地返回该行.

The key point in the above query is the usage of a derived table that returns the latest revision, i.e. MAX(rev_id), per dwg_id. Using an INNER JOIN on that derived table you get back exactly this row out of dwg_rev table.

如果每个project_no具有多个 dwg_id，则必须使用上述类似内容.在这种情况下，上述查询将为每个图形获取project_no = 153的最新修订.

Using something like the above is necessary if you have multiple dwg_id per project_no. In this case, the above query will fetch the most recent revision per drawing for project_no = 153.

此处演示

这篇关于联接2个表并仅显示id的最大值-mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！