left-join-on-max-date-value和order-by-id-desc

u0njafvf 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(303)

假设我有以下两个表：

lead
id(PK) status   assigned_to   date
1      open     Smith         2018-08-26
2      open     Drew          2018-08-26
3      new      Amit          2018-08-26

lead_comments
id  lead_id(FK)  comment_data        follow_up_time
1      1         old task line2      2018-08-27 14:18:26
2      2         old task line1      2018-08-27 14:18:26
3      1         new task line1      2018-08-27 17:18:00
4      2         new task line3      2018-09-27 20:18:26
5      2         old task line2      2018-08-27 21:18:26

现在，我需要一个mysql查询来选择每个lead匹配的最新评论（如果有）order by latest follow_up_time 从lead\u comments表。
我的预期结果：

lead_id comment _id follow_up_time       comment_data    assigned_to    
2        4          2018-09-27 20:18:26  new task line3  Drew          
1        3          2018-08-27 17:18:00  new task line1  Smith
3        Null       Null                 Null            Amit

我正在尝试：

SELECT l.id as lead_id,
       l.status as status,
       c.id as comment_id,
       c.comment_date as comment_date, 
       c.comment_data as comment,
       c.commented_by,
       l.assigned_to 
FROM lms_leads l 
LEFT JOIN lms_leads_comments as c ON l.id=c.lead_id 
JOIN (
    SELECT max(cm.id) as id
    FROM lms_leads_comments cm
    GROUP BY cm.lead_id 
) as cc ON c.id=cc.id 
GROUP BY l.id 
ORDER BY c.follow_up_time DESC

但是，这个查询没有按照我的预期结果工作
请建议我如何实现我的目标？

mysql Join Database left-join maxdate

来源：https://stackoverflow.com/questions/52243277/left-join-on-max-date-value-with-order-by-id-desc

2条答案

按热度按时间

6za6bjd01#

你可以用 ROW_NUMBER （mysql 8.0版）：

SELECT *
FROM (SELECT l.*, c.comment_data, c.follow_up_time,
         ROW_NUMBER() OVER(PARTITION BY l.id ORDER BY c.follow_up_time DESC) AS rn
      FROM lms_leads l 
      LEFT join lms_leads_comments as c ON l.id=c.lead_id) s
WHERE rn = 1;

dbfiddle演示

赞(0）回复(0）举报 2021-06-20

qvk1mo1f2#

试试这个（对于mysql版本<8.0）：

select @rn := 1, @lead_id_lag := 0;

select l.id lead_id, 
       lc.id comment_id,
       lc.follow_up_time,
       lc.comment_data,
       l.assigned_to
from lead l
left join (
    select case when @lead_id_lag = lead_id then @rn := @rn + 1 else @rn := 1 end rn,
           @lead_id_lag := lead_id,
           id,
           follow_up_time,
           comment_data,
           lead_id
    from lead_comments
    order by lead_id, follow_up_time desc
) lc on l.id = lc.lead_id and lc.rn = 1;

演示

赞(0）回复(0）举报 2021-06-20

我来回答

left-join-on-max-date-value和order-by-id-desc

2条答案

相关问题

热门标签

最新问答