Mysql join select max for all record

问题描述：

我无法按照我的预期映射记录.

I am unable to map the record as my expectation.

文档表

-------+-------------------
doc_id | doc_title         
-------+-------------------
1      | My book
-------+-------------------
2      | My sec Book 
--------------------------

文档详情表

-----------+--------------+-----------------------
fk_doc_id  |  doc_version |  submit_date
-----------+--------------+-----------------------
1          |   1          |  2015-10-25 14:32:01
-----------+--------------+-----------------------
1          |   2          |  2015-10-26 13:00:01
-----------+--------------+-----------------------
1          |   3          |  2015-10-27 09:00:00 
--------------------------+-----------------------
2          |   1          |  2015-10-25 11:15:01
-----------+--------------+-----------------------
2          |   2          |  2015-10-26 10:00:00
--------------------------+-----------------------

问题:如何加入这两个表以获取每个文档的最新版本文档信息?即使我获得了最新版本，但行信息不正确.

Question: How do I join this two tables to get each documents with the latest version doc info? even though I get the latest version but the row info which is not correct.

到目前为止我已经尝试过这个查询

So far I have tried this query

SELECT *, max(doc_version) AS latest_version
FROM d_doc
JOIN d_doc_dtl ON d_doc.doc_id = d_doc_dtl.fk_doc_id
GROUP BY d_doc.doc_id;

我的预期结果是

--------+--------------+----------------+--------------------
doc_id  |  doc_title   | latest_version | submit_date
--------+--------------+----------------+--------------------
1       |  My book     | 3              | 2015-10-27 09:00:00
--------+--------------+----------------+--------------------
2       | My sec book  | 2              | 2015-10-26 10:00:00
----------------------------------------+--------------------

但我的结果是

--------+--------------+----------------+--------------------
doc_id  |  doc_title   | latest_version | submit_date
--------+--------------+----------------+--------------------
1       |  My book     | 3              | 2015-10-25 14:32:01
--------+--------------+----------------+--------------------
2       | My sec book  | 2              | 2015-10-25 11:15:01
----------------------------------------+--------------------

注意:submit_date 不正确.

NOTE: the submit_date which is no correct.

答

此查询应按预期工作.它在内部子查询中选择最新的文档版本，然后将其与文档连接起来.

This query should work as you expect. It selects latest document versions in inner subquery and than joins it with documents.

SELECT d.doc_id,
       d.doc_title,
       dtl.doc_version latest_version,
       dtl.submit_date
  FROM d_doc d
 INNER JOIN (SELECT dt.*
               FROM d_doc_dtl dt
              INNER JOIN (SELECT fk_doc_id, MAX(doc_version) doc_version 
                            FROM d_doc_dtl
                           GROUP BY fk_doc_id) dm
                 ON dt.fk_doc_id = dm.fk_doc_id
                AND dt.doc_version = dm.doc_version) dtl
    ON d.doc_id = dtl.fk_doc_id

你得到错误的结果，因为你只选择了 max(version)，但 date 因为它不在 group by 子句中可以包含任何值.首先，您需要获取包含最新版本的记录，如上所示.

You get wrong results because you selected only max(version), but date as it is not in group by clause can contain any value. First you need to get records containing latest version as shown above.

Mysql join select max for all record

相关推荐