LEFT JOIN на значении максимальной даты с ORDER BY id DESC

Предположим, у меня есть две следующие таблицы:

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

Однако этот запрос не работает в соответствии с моим ожидаемым результатом.

Подскажите, пожалуйста, как я могу достичь того, что пытаюсь сделать?

почему old task line2 не последний комментарий for lead_id = 2

Madhur Bhaiya 09.09.2018 11:49

потому что follow_up_time - это 2018-08-27 21:18:26 <2018-09-27 20:18:26

amit gupta 09.09.2018 11:53
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
2
34
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Вы можете использовать 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

это дает мне ошибку: правильный синтаксис для использования рядом с '(PARTITION BY l.id ORDER BY c.follow_up_time DESC) AS rn FROM lms_leads l' в строке 3

amit gupta 09.09.2018 11:56

@amitgupta MySQL 8.0 и выше

Lukasz Szozda 09.09.2018 11:56

можем ли мы добиться этого на MySQL Server версии: 5.6.40

amit gupta 09.09.2018 12:03

это не дает мне ожидаемого результата: dbfiddle.uk/…

amit gupta 09.09.2018 12:17

@amitgupta Демо - при копировании кода убедитесь, что вы скопировали все

Lukasz Szozda 09.09.2018 12:37
Ответ принят как подходящий

Попробуйте это (для версии 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/…

amit gupta 09.09.2018 12:18

@amitgupta Вы пробовали выложенную мной демонстрацию? Этот сайт лучше поддерживает MySQL, и там он работает.

Michał Turczyn 09.09.2018 12:22

да, я пробовал вашу демонстрацию, но при нажатии на кнопку запуска возникла непредвиденная ошибка :(

amit gupta 09.09.2018 12:25

@amitgupta не возможно, при входе на сайт работает. Дад ты пробовать что-нибудь менять?

Michał Turczyn 09.09.2018 12:29

о, это была моя проблема. да, теперь работает, спасибо :)

amit gupta 09.09.2018 12:34

@amitgupta Затем вы должны принять ответ (зеленая галочка слева) и, при желании, проголосовать за.

Michał Turczyn 09.09.2018 12:37

можем ли мы подсчитать общее количество комментариев к каждому лиду с помощью самого этого запроса? пожалуйста, также помогите мне с этим, это мне очень поможет

amit gupta 09.09.2018 21:28

Другие вопросы по теме