如何从左外部连接中检索最大日期？

Question

我有2个表client_headquarter & clients

client_headquarter: id

客户端: id，headquarterid，clentname，tscreated

我想要显示所有总部，以及每个总部显示“最近的”客户(ts_created)，如果它存在的话，在它的位置上是空白的。我希望所有这些都按照总部的顺序进行排序，这些总部没有客户，老客户和最新客户在底部。

有没有人能帮我解决这个问题？

Answer 1

SELECT client_headquarter.id, max(clients.tscreated)
FROM client_headquarter 
LEFT OUTER JOIN clients ON clients.headquarterid = client_headquarter.id
GROUP BY client_headquarter.id
ORDER BY MAX(clients.tscreated) ASC

Answer 2

就像..。

> select chq.id, count(clientname), max(tscreated) from clients c
> left outer join client_headquarter chq on c.hearquarterid = chq.id
> group by chq.id
> order by count(clientname) DESC, max(tscreated) DESC

Answer 3

select 
    ch.id as [Headquarter ID],
    c.clientname as [Most Recent Client Name],
    c.tscreated as [Date Created]
from 
    client_headquarter ch
    left join 
    (select 
        headquarterid,
        max(tscreated)
    from
        clients
    group by
        headquarterid
    ) recent on recent.headquarterid = ch.id
    left join clients c on c.headquarterid = ch.headquarterid and c.tscreated = recent.tscreated
order by
    c.tscreated

如果多个客户具有相同的创建日期，这将为单个总部创建多个行。如果这是不可取的，那么必须定义和实现一些明确的区分获胜者的方法。