在分组内排序? [英] Order within group by?

问题描述

在我的系统中，我有客户.客户有程序.我想显示一个客户端列表，显示其最近的活动程序(如果存在).

In my system, I have clients. Clients have programs. I want to display a list of clients, showing their most recent active (if it exists) program.

因此，我们有这样的东西:

Thus, we have something like this:

SELECT * 
FROM clients AS client 
    JOIN programs AS program ON client.id=program.client_id
GROUP BY client.id
ORDER BY program.close_date=0 DESC, program.close_date DESC

close_date=0表示程序未关闭.因此，它将首先放置未关闭的程序，然后放置最近关闭的程序.

close_date=0 means the program isn't closed. So it will put the non-closed programs first, and then the most recently closed programs next.

问题是，排序依据在组内不起作用.它只是随机选择一种程序.我该如何解决?

Problem is, the order by doesn't work within the groups. It just kind of picks one of the programs at random. How do I resolve this?

只是想出了这个:

SELECT * 
FROM clients AS client 
    JOIN (SELECT * FROM programs AS program ORDER BY program.close_date=0 DESC, program.close_date DESC) AS program ON client.id=program.client_id
GROUP BY client.id

似乎给出正确的结果.这是正确的，还是我很幸运?即，我基本上已经在对表进行排序之前对其进行了排序；这些结果将像连接一样保持排序，对吧?

Which seems to give correct results. Is this correct, or am I just getting lucky? i.e., I've essentially sorted the table before joining on it; those results will stay sorted as it does the join, right?

解决方案:我现在认为这是经典的按组分配的最大值

Solution: I now believe this a classic group-wise maximum problem. Search for that if you're stuck on a similar problem. The solution involves joining the same table twice.

推荐答案

SELECT  c.*, p.*
FROM    clients AS c
JOIN    programs AS p
ON      p.id = 
        (
        SELECT  pi.id
        FROM    programs AS pi
        WHERE   pi.client_id = c.id
        ORDER BY
                pi.close_date=0 DESC, pi.close_date DESC
        LIMIT 1
        )

感谢，请访问 @Quassnoi .在类似(但更复杂)的问题中查看他的答案: mysql-按显示最新结果分组

Thanx should go to @Quassnoi. See his answer in a similar (but more complicated) question: mysql-group-by-to-display-latest-result

如果更新programs表并将所有零记录的close_date设置为close_date='9999-12-31'，则ORDER BY会更简单(使用适当的索引，整个查询会更快):

If you update the programs table and set close_date for all records that it is zero to close_date='9999-12-31', then your ORDER BY will be simpler (and the whole query faster with proper indexes):

        ORDER BY
                pi.close_date DESC

这篇关于在分组内排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！