我有两个表，其中包含有关我在查询中加入的图形的信息。第一个表格包含图纸的唯一编号、标题和绘制者。第二个表格包含图纸的修订和修订日期。
表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 |
我想连接两个表，只显示图形的最新修订更改。
这是我当前的查询。
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
此查询返回的结果不包含最新的修订版本号，或者返回每个图形的所有修订版本。
您可以使用以下查询：
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
上述查询中的关键点是返回最新修订版的派生表的使用，即 MAX(rev_id)，每dwg_id。使用该派生表上的INNER JOIN，您可以从表中准确返回dwg_rev行。
如果您每project_no有多个dwg_id，则必须使用上述内容。在这种情况下，上述查询将获取每个图形的最新修订project_no = 153。
在这里演示
有时MAX不是最好的方法，而是使用LIMIT试试这个：
SELECT
`drawings`.`dwg_id`,
`project_no`,
`sws_dwg_no`,
`client_dwg_no`,
`dwg_title`,
`dwg_by`,
`dwg_rev`.`dwg_rev`,
`dwg_rev`.`dwg_date`,
`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` DESC,
`dwg_rev`.`dwg_rev` DESC
LIMIT 1;
如果您需要最新版本，您应该订购下面的 DESC 检查代码。
而且您只能通过 dwg_rev . rev_id' DESC 订购，如果填充了此rev_id。
SELECT
图纸. 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
(图纸)
JOIN dwg_rev ON图纸. 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 DESC,
dwg_rev . dwg_rev DESC
LIMIT 1;