Заказать внутри группы по?

В моей системе у меня есть клиенты. У клиентов есть программы. Я хочу отобразить список клиентов, показав их последнюю активную (если она существует) программу.

Таким образом, мы имеем что-то вроде этого:

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 означает, что программа не закрыта. Таким образом, сначала будут помещены незакрытые программы, а затем самые последние закрытые программы.

Проблема в том, что порядок не работает внутри групп. Он просто выбирает одну из программ наугад. Как решить эту проблему?

Только что придумал:

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

Что, кажется, дает правильные результаты. Это правильно, или мне просто повезло? т. е. я по существу отсортировал таблицу, прежде чем присоединиться к ней; эти результаты останутся отсортированными по мере объединения, верно?

Решение: теперь я считаю, что это классическая проблема группового максимума . Ищите это, если вы застряли на похожей проблеме. Решение включает в себя присоединение к одной и той же таблице дважды.


mysql greatest-n-per-group
person mpen    schedule 29.08.2011    source источник
comment
ORDER BY в подвыборке не должен оказывать детерминированного влияния на результат. Это может работать с MySQL, но стандарт SQL даже не позволяет этого. Ключевым моментом является ORDER BY client.id в основном (внешнем запросе), а затем по любым другим столбцам, которые вы хотите. Возможно, вам придется сказать одно и то же в предложениях GROUP BY и ORDER BY; это ничего не стоит.   -  person Jonathan Leffler    schedule 29.08.2011
comment
@Mark: я не думаю, что ваше альтернативное решение (опубликованное выше) гарантированно даст правильные результаты. После выполнения GROUP BY для client.id сервер может выбрать любую запись из каждой группы.   -  person unutbu    schedule 30.08.2011
comment
@ubutbu: Спасибо! Это именно то, что я хотел знать. Я волновался, что может быть так.   -  person mpen    schedule 30.08.2011

Ответы (2)


arrow_upward
8
arrow_downward 

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- группировать по последнему результату

Если вы обновите таблицу programs и установите close_date для всех записей, что она равна close_date='9999-12-31', то ваша ORDER BY будет проще (и весь запрос быстрее с правильными индексами):

        ORDER BY
                pi.close_date DESC
person ypercubeᵀᴹ    schedule 29.08.2011
comment
Ах... да, это объединение имеет гораздо больше смысла. Большое спасибо! - person mpen; 30.08.2011
comment
Это ответ, который я искал повсюду. - person atomkirk; 07.02.2018

arrow_upward
1
arrow_downward

Попробуйте этот порядок по пункту...

ORDER BY client.id, CASE WHEN program.close_date = 0 THEN 0 ELSE 1 END, program.close_date DESC
person JSR    schedule 29.08.2011
comment
Я не понимаю, как это что-то дает. program.close_date=0 уже будет оцениваться как 0 или 1. Проблема в том, что мне нужно сортировать по группам, общая сортировка (после группировки) в порядке. - person mpen; 29.08.2011
comment
Первый столбец в списке — это client.id..., который будет держать ваши группы вместе. - person JSR; 29.08.2011
comment
О ..... вы должны были указать это в своем ответе. Хотя я до сих пор не уверен на 100%, что вы имеете в виду. Помещение client.id на первое место заставит порядок работать? Почему это имеет значение? - person mpen; 30.08.2011
comment
Я не уверен, что понимаю, почему вы используете GROUP BY в первую очередь ... обычно GROUP BY используется, когда вам нужна SUM, COUNT или другой агрегат из возвращаемых строк. Во всяком случае, ORDER BY превосходит GROUP BY в том, что касается сортировки возвращаемых строк. - person JSR; 30.08.2011
comment
Я использую GROUP BY, потому что я хочу перечислить каждого клиента только один раз, но я хочу показать его самые последние данные программы. Что вы имеете в виду под ORDER BY козырями GROUP BY? Мне кажется, что порядок применяется после группировки. Мне нужно применить некоторый порядок в каждой группе, чтобы я мог получить только определенную программу для каждого клиента. Фактический порядок списка клиентов в целом является отдельной (не)проблемой. - person mpen; 30.08.2011