mysql - 如何编写一个 MySQL 查询，其中两个表有条件？

Question

表格...

contracts
---------
id

contract_locations
------------------
id
contract_id     # A contract can have many contract locations.
name            # E.g., "Front office", "Legal", "Contracts admin", etc.
arrival_date

用户将最后 arrival_date 的位置视为给定合约的“当前位置”。

他们希望能够找到当前位置名称等于(例如)“前台”或“法律”的所有契约(Contract)。

如何编写 MySQL 5.1 查询来执行此操作？

Answer 1

这是一种方法:

select c.id
from (select c.*,
             (select name
              from contract_locations cl
              where cl.contract_id = c.id
              order by arrival_date desc
              limit 1
             ) CurrentLocation
      from contracts c
     ) c
where CurrentLocation = 'Front Office'

这使用相关子查询来获取当前位置。通过在 Contract_Locations(contract_id,arrival_date) 上建立索引，性能将大大提高。

这是另一种可能不太明显的方法。其想法是查看最近的日期是否是给定位置的最近的日期。这使用了 having 子句:

select contract_id
from contract_locations cl
group by contract_id
having max(arrival_date) = max(case when name = 'Front Office' then arrival_date end)

仅当'Front Office'(或其他)为最近日期时，having 子句才为真。