连接2个表，只显示id的最大值 - mysql

3 回答

• 1

  您可以使用以下查询：

  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 的每个绘图的最新修订 .

  Demo here

  回复于 2024-05-16T05:52:35+08:00
• 0

  有时 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;
  

  回复于 2024-05-16T05:52:35+08:00
• 0

  如果您需要最新版本，请在下面订购DESC检查代码 .

  如果填充此rev_id，您也可以仅通过dwg_rev . rev_id` DESC进行订购 .

  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;

  回复于 2024-05-16T05:52:35+08:00