mysql - 完成 MAX() 但使用更多表中的数据

Question

好吧，我最终在一张表上使用 max() 正确拉取了 max() - 我花了一些时间来理解发生了什么，并在使用它时阅读 mysql 的限制

我花了一些时间在 sqlfiddle 上做了一些演示数据(下面的数据只是一张表上的最大值

http://sqlfiddle.com/#!2/fff224/1

我想要的(并且吸收了我在过去两个小时中尝试让它工作的方法)是如何合并另一个不需要使用 max 的表(我已将这些包含在 sqlfiddle 中

我想要的结果是

case_number full_address    case owner  client  compiled date(max())    recommendation
1000      1 high street       bob     london        14/12/2012                   let
1001      2 high street       ken     Compton       13/12/2013                   sell
1002      3 high street      ken      Leeds        14/12/2013                    sell

完成从 client\staff\到案例流程之间的内部联接，我很好，只是这个嵌套的选择最大我跌倒了

Answer 1

SELECT p.case_number
     , p.full_address
     , s.case_owner_name 'case owner'
     , c.client_name client
     , r.compiled_date
     , r.recommendation
  FROM case_process p
  JOIN staff s
    ON s.case_owner_number = p.case_owner_number
  JOIN client c
    ON c.client_number = p.client_number
  JOIN reporting r
    ON r.case_number = p.case_number
  JOIN (SELECT case_number,MAX(compiled_date) max_compiled_date FROM reporting GROUP BY case_number) x
    ON x.case_number = r.case_number
   AND x.max_compiled_date = r.compiled_date;

 +-------------+---------------+------------+---------+---------------+----------------+
 | case_number | full_address  | case owner | client  | compiled_date | recommendation |
 +-------------+---------------+------------+---------+---------------+----------------+
 |        1000 | 1 high street | Bob        | London  | 2012-12-14    | let            |
 |        1001 | 2 high street | Ken        | Compton | 2013-12-13    | sell           |
 |        1002 | 3 high street | Ken        | Leeds   | 2012-12-14    | sell           |
 +-------------+---------------+------------+---------+---------------+----------------+
 3 rows in set (0.00 sec)

 fiddle of same... http://sqlfiddle.com/#!2/fff224/7