MySQL在同一列上两次将同一表连接在一起，但值不同，仅返回最新的行 [英] MySQL join same table twice on same column with different value returning most recent row only

问题描述

在解决复杂的JOIN的一小部分方面，我陷入了困境.

I'm stuck trying to solve a small part of what is otherwise a complex JOIN.

我们有一个说明"表和一个估计"表.在估算"中，对于给定指令，对于不同类型的estimates，我们有多行.

We have an 'instructions' table and an 'estimates' table. In the 'estimates' we have multiple rows for different types of estimates for a given instruction.

说明表

id | address | status
1 | 27 TAYLOR ROAD, ALBION PARK NSW 2527 | InProgress

估计表

id | instruction_id | basis | basis_date | basis_value
1 | 1 | ContractPrice | 2012-04-05 | 124000
2 | 1 | CAMV | 2012-02-01 | 120000
3 | 1 | CustomerEstimate | 2012-06-07 | 132000
4 | 1 | ContractPrice | 2013-01-03 | 140000
5 | 1 | CustomerEstimate | 2013-02-09 | 145000

实际上，我们想要的是根据指令基于"estimates"的"instructions"的两个联接.id= estimates.instruction_id和estimates.basis为1)最新的"CustomerEstimate"(别名为basic_date和basic_value的别名为estimate_date和estimate_value)和2)最新的合同价格"(再次，将base_date和base_value别名为contact_date和contract_value).

What we want is actually 2 joins of 'instructions' on 'estimates' based on instructions.id = estimates.instruction_id and estimates.basis for 1) the most recent 'CustomerEstimate' (aliasing basis_date and basis_value as estimate_date and estimate_value) and 2) most recent 'ContractPrice' (again, aliasing basis_date and basis_value as contact_date and contract_value).

预期结果如下；

id | address | status | contract_price | contract_date | estimate_date | estimate_value
1 | 27 TAYLOR ROAD, ALBION PARK NSW 2527 | InProgress | 2013-01-03 | 140000 | 2013-02-09 | 145000

我真的很感谢SQL专家的帮助.

I would really appreciate some assistance from the SQL gurus out there.

非常感谢， 特伦特.

推荐答案

尝试

SELECT i.id,  
       i.address, 
       i.status,
       p.max_date contract_date, 
       p.basis_value contract_price, 
       e.max_date estimate_date, 
       e.basis_value estimate_value
  FROM Instructions i LEFT JOIN
(
    SELECT q1.instruction_id, max_date, basis_value
      FROM Estimates e JOIN
    (
        SELECT instruction_id, MAX(basis_date) max_date
          FROM Estimates
         WHERE basis = 'CustomerEstimate'
         GROUP BY instruction_id
    ) q1 ON e.instruction_id = q1.instruction_id AND e.basis_date = q1.max_date
) e ON i.id = e.instruction_id LEFT JOIN
(
    SELECT q2.instruction_id, max_date, basis_value
      FROM Estimates e JOIN
    (
        SELECT instruction_id, MAX(basis_date) max_date
          FROM Estimates
         WHERE basis = 'ContractPrice'
         GROUP BY instruction_id
    ) q2 ON e.instruction_id = q2.instruction_id AND e.basis_date = q2.max_date
) p ON i.id = p.instruction_id

输出:

| ID |                              ADDRESS |     STATUS | CONTRACT_PRICE | CONTRACT_DATE | ESTIMATE_VALUE | ESTIMATE_DATE |
----------------------------------------------------------------------------------------------------------------------------
|  1 | 27 TAYLOR ROAD, ALBION PARK NSW 2527 | InProgress |         140000 |    2013-01-03 |         145000 |    2013-02-09 |

这里是 SQLFiddle 演示.

Here is SQLFiddle demo.

这篇关于MySQL在同一列上两次将同一表连接在一起，但值不同，仅返回最新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！