Предположим, у меня есть две следующие таблицы:
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, который выбрал бы каждый лид совпадает с последним комментарием (если есть) Заказ по последней версии follow_up_time
из таблицы lead_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
Однако этот запрос не работает в соответствии с моим ожидаемым результатом.
Подскажите, пожалуйста, как я могу достичь того, что пытаюсь сделать?
потому что follow_up_time - это 2018-08-27 21:18:26 <2018-09-27 20:18:26
Вы можете использовать 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;
это дает мне ошибку: правильный синтаксис для использования рядом с '(PARTITION BY l.id ORDER BY c.follow_up_time DESC) AS rn FROM lms_leads l' в строке 3
@amitgupta MySQL 8.0 и выше
можем ли мы добиться этого на MySQL Server версии: 5.6.40
это не дает мне ожидаемого результата: dbfiddle.uk/…
@amitgupta Демо - при копировании кода убедитесь, что вы скопировали все
Попробуйте это (для версии 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;
это не дает мне ожидаемого результата, проверьте это: dbfiddle.uk/…
@amitgupta Вы пробовали выложенную мной демонстрацию? Этот сайт лучше поддерживает MySQL, и там он работает.
да, я пробовал вашу демонстрацию, но при нажатии на кнопку запуска возникла непредвиденная ошибка :(
@amitgupta не возможно, при входе на сайт работает. Дад ты пробовать что-нибудь менять?
о, это была моя проблема. да, теперь работает, спасибо :)
@amitgupta Затем вы должны принять ответ (зеленая галочка слева) и, при желании, проголосовать за.
можем ли мы подсчитать общее количество комментариев к каждому лиду с помощью самого этого запроса? пожалуйста, также помогите мне с этим, это мне очень поможет
почему
old task line2
не последний комментарийfor lead_id = 2