Nel mio sistema, ho clienti. I clienti hanno programmi. Voglio visualizzare un elenco di client, mostrando il loro programma attivo più recente (se esiste).
Quindi, abbiamo qualcosa di simile a questo:
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 significa che il programma non è chiuso. Quindi metterà prima i programmi non chiusi e poi quelli chiusi più di recente.
Il problema è che l’ordine non funziona all’interno dei gruppi. È solo una specie di scegliere uno dei programmi a caso. Come posso risolvere questo?
Ho appena trovato questo:
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
Che sembra dare risultati corretti. È corretto, o sto solo diventando fortunato? cioè, ho essenzialmente ordinato la tabella prima di unirla; quei risultati rimarranno ordinati come fa il join, giusto?
Soluzione: ora credo che questo sia il classico problema massimo di gruppo . Cercalo se sei bloccato su un problema simile. La soluzione prevede di unire due volte lo stesso tavolo.
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 )
Grazie dovrebbe andare a @Quassnoi . Vedi la sua risposta in una domanda simile (ma più complicata): mysql-group-by-to-display-latest-result
Se aggiorni la tabella programs e imposta close_date per tutti i record che è zero per close_date='9999-12-31' , allora il tuo ORDER BY sarà più semplice (e l’intera query più veloce con gli indici appropriati):
ORDER BY pi.close_date DESC
Prova questo ordine per clausola …
ORDER BY client.id, CASE WHEN program.close_date = 0 THEN 0 ELSE 1 END, program.close_date DESC