MYSQL 连接未选择正确的行

Question

我有两张 table

1)外展

id  profile_id  url
-------------------------
1    2        www.test.com
2    3        www.google.com 
3    4        www.example.com

2).外展链接

id  outreach_id  end_date             status
------------------------------------
1    1           2016-12-28 00:00:00  Approved
2    1           2016-12-16 00:00:00  Approved
3    1           NUll                 Pending
4    1           2016-12-11 00:00:00  Approved

我有这个带有左连接和条件的 SQL 查询，工作正常，但我想选择满足 3 条件的 MAX end_date 的整个 ROW。所以在这种情况下，第一行 end_date = 2016-12-28 00:00:00

select o.*,ol.*,MAX(ol.end_date) as max_date, SUM(ol.status = "Approved" and (ol.end_date > Now() or end_date is null)) as cond1, SUM(ol.status = "Pending") as cond2,
    SUM(ol.status = "Approved" and (ol.end_date < Now() and ol.end_date is not null)) as cond3
FROM outreach o 
LEFT JOIN outreach_links ol on ol.outreach_id = o.id 
WHERE o.profile_id=2 
GROUP BY o.id
HAVING (cond1 = 0 and cond2 = 0) or (cond1 = 0 and (cond2 = 1 and cond3 >=1)) 
ORDER BY ol.end_date desc

但这是此查询的输出(由于某种原因它选择了待处理)>>

+"id": "3"
+"profile_id": "2"
+"url": "www.test.com"
+"outreach_id": "1"
+"end_date": null
+"status": "Pending"
+"max_date": "2016-12-28 00:00:00"
+"cond1": "0"
+"cond2": "1"
+"cond3": "3"

我想要这个

+"id": "1"
+"profile_id": "2"
+"url": "www.test.com"
+"outreach_id": "1"
+"end_date": 2016-12-28 00:00:00
+"status": "Approved"
+"max_date": "2016-12-28 00:00:00"
+"cond1": "0"
+"cond2": "1"
+"cond3": "3"

第一行包含最大结束日期，我该如何保持相同的查询？

谢谢

Answer 1

请参阅 SQL Select only rows with Max Value on a Column 了解如何获取每个 outreach_id 的最大 end_date 行。然后加入该行以获取最新状态。

SELECT o.*, ol1.max_date, ol2.status, SUM(ol.status = "Approved" and (ol.end_date > Now() or end_date is null)) as cond1, SUM(ol.status = "Pending") as cond2,
    SUM(ol.status = "Approved" and (ol.end_date < Now() and ol.end_date is not null)) as cond3
FROM outreach o 
LEFT JOIN outreach_links AS ol ON ol.outreach_id = o.id
LEFT JOIN (SELECT outreach_id, MAX(end_date) AS max_date
           FROM outreach_links
           GROUP BY outreach_id) AS ol1 ON ol1.outreach_id = o.id
LEFT JOIN outreach_links ol2 on ol2.outreach_id = o.id AND ol2.end_date = ol1.max_date
WHERE o.profile_id=2 
GROUP BY o.id
HAVING (cond1 = 0 and cond2 = 0) or (cond1 = 0 and (cond2 = 1 and cond3 >=1)) 
ORDER BY ol.end_date desc