我有一个包含所有消息的消息表。我想在将电话号码与员工表匹配的同时,按电话号码进行查询和分组,并返回该电话号码的最新消息/时间。
我现在使用的查询会给我它找到的第一条消息,而不是最新的消息。不过,其他一切都很完美。
员工表
id,firstname,lastname,phonenumber,crew
消息表
id,type,phonenumber,message,status,date
查询我现在正在使用
select V.firstname,V.lastname,U.phonenumber,U.message,U.read,
max(U.date) as last_date
from messages as U left join employees as V
on V.phonenumber = U.phonenumber
group by U.phonenumber
order by last_date desc
要获得每个电话号码的最后一条消息日期,可以使用以下查询:
select phonenumber, max(date) as max_date
from messages
group by phonenumber
要获得每个电话号码的最后一条消息,您必须使用消息表加入上一个查询:
select m.*
from
messages m inner join (
select phonenumber, max(date) as max_date
from messages
group by phonenumber
) lm on m.phonenumber = lm.phonenumber and m.date=lm.max_date
然后你可以加入员工表:
... inner join employees as v
on m.phonenumber = v.phonenumber
您可以使用Right Join来处理它
您可以通过使用带组的子选择
select V.firstname,V.lastname,U.phonenumber,U.message,U.read, U.date
from messages as U left join employees as V
on V.phonenumber = U.phonenumber
where (U.date, U.phonenumber) in (select phonenumber, max(date)
from messages group by phonenumber)
order by U.date desc
以下是一个可能有所帮助的示例:
select firstname,lastname,phonenumber from employees
left join messages
on employees.phonenumber = messages.phonenumber
where messages.date = (select max(date) from messages)
示例来自:http://www.w3resource.com/sql/aggregate-functions/max-date.php