时间戳

首页 > 解决方案 > MySQL中“Group By”的“Order by”结果

mysql - MySQL中“Group By”的“Order by”结果

问题描述

我有以下表结构

leads
-----
id name

1   Varun
2   Vishal

visitors
--------
id name
1   Rahul
2   Akhshay

assigned_visitors
-----------------
av_id lead_id, visitor_id, visit_date
1   1       1           2020-11-28
2   1       2           2020-12-02

不，我需要显示以下结果集

leads.name   visitors.name    visit_date    
Varun        Akhshay          2020-12-02

以下 SQL 将显示正确的 visit_date 但不正确的访客姓名。

select l.*
     , l.Name as LeadName
     , l.Phone as LeadPhone
     , u.*
     , u.Name as VisitorName
     , u.Phone as VisitorPhone
     , SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT visit_date ORDER BY av_id DESC SEPARATOR ','), ',', 1) as max_visit_date 
  from leads as l 
  join assigned_visitors a 
    on l.id = a.lead_id 
  join users u 
    on u.user_id = a.visitor_id 
 where l.Status = 3 
 group 
    by l.id 
 HAVING (date(max_visit_date) BETWEEN '$startdate' AND '$enddate') 
 order 
    by max_visit_date DESC

任何帮助都感激不尽。

标签： mysqlsql

解决方案

您的查询比您显示的数据复杂得多。如果我假设您想要每个潜在客户的最近访问，那么一种方法是相关子查询：

select l.*, av.*, u.*
from leads l join
     assigned_visitors av
     on l.id = av.lead_id join
     users u 
     on u.user_id = av.visitor_id 
where l.Status = 3 and
      av.visit_date = (select max(av2.visit_date)
                       from assigned_visitors av2
                       where av2.lead_id = av.lead_id
                      );

聚合不会是此类查询的有效方法。

推荐阅读