MySQL groupwise MAX () restituisce risultati imprevisti

TABELLA: PRESTITO

Loan_no Amount SSS_no Loan_date 7 700.00 0104849222 2010-01-03 8 200.00 0104849222 2010-02-28 9 300.00 0119611199 2010-11-18 10 150.00 3317131410 2012-11-28 11 600.00 0104849222 2011-01-03 14 175.00 3317131410 2012-12-05 15 260.00 3317131410 2013-02-08 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10 

RISULTATI DESIDERATI:

Vorrei recuperare l’ultimo prestito messo a disposizione da ogni persona (identificato dal loro numero SSS). I risultati dovrebbero essere i seguenti:

 Loan_no Amount SSS_no Loan_date 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10 

QUERY # 1 USATO:

SELECT * FROM loan GROUP BY SSS_no ORDER BY Loan_date DESC

RISULTATO MYSQL

 Loan_no Amount SSS_no Loan_date 10 150.00 3317131410 2012-11-28 9 300.00 0119611199 2010-11-18 7 700.00 0104849222 2010-01-03 

QUERY # 2 USATO:

SELECT Loan_no, Amount, SSS_no, max(Loan_date) FROM loan GROUP BY SSS_no

RISULTATO MYSQL

 Loan_no Amount SSS_no Loan_date 7 700.00 0104849222 2013-03-06 9 300.00 0119611199 2011-04-30 10 150.00 3317131410 2013-03-10 

Qualcuno può aiutarmi con il mio problema? Grazie.

Prova questo invece:

 SELECT l1.* FROM loan AS l1 INNER JOIN ( SELECT SSS_no, MAX(Loan_date) LatestDate FROM loan GROUP BY SSS_no ) AS l2 ON l1.SSS_no = l2.SSS_no AND l1.loan_date = l2.LatestDate; 

SQL Fiddle Demo

Questo ti darà:

 | LOAN_NO | AMOUNT | SSS_NO | LOAN_DATE | ---------------------------------------------- | 16 | 230 | 104849222 | 2013-03-06 | | 17 | 265 | 119611199 | 2011-04-30 | | 18 | 455 | 3317131410 | 2013-03-10 | 

Il motivo per cui ottieni risultati imprevisti è perché stai utilizzando GROUP BY su una sola colonna nell’elenco SELECT e non utilizzi alcuna funzione di aggregazione su tutte le colonne.

MySQL utilizza un’estensione della funzione GROUP BY che può causare risultati imprevisti quando non si esegue il GROUP BY o si aggregano tutti gli elementi nell’elenco SELECT . (vedi Estensioni MySQL su GROUP BY )

Dai documenti MySQL:

MySQL estende l’uso di GROUP BY in modo che l’elenco di selezione possa fare riferimento a colonne non aggregate non denominate nella clausola GROUP BY. … È ansible utilizzare questa funzione per ottenere prestazioni migliori evitando l’ordinamento e il raggruppamento non necessari delle colonne. Tuttavia, ciò è utile principalmente quando tutti i valori in ogni colonna non aggregata non denominata in GROUP BY sono uguali per ciascun gruppo. Il server è libero di scegliere qualsiasi valore da ciascun gruppo, quindi a meno che non siano gli stessi, i valori scelti sono indeterminati. Inoltre, la selezione dei valori di ciascun gruppo non può essere influenzata dall’aggiunta di una clausola ORDER BY. L’ordinamento del set di risultati si verifica dopo aver scelto i valori e ORDER BY non influisce sui valori scelti dal server.

L’unico modo in cui puoi essere sicuro di restituire il risultato corretto è modificare la query in modo aggregato e GROUP BY correttamente.

Quindi puoi usare qualcosa di simile a questo:

 select l1.loan_no, l1.amount, l1.SSS_no, l1.loan_date from loan l1 inner join ( select SSS_no, max(loan_date) Loan_date from loan group by SSS_no ) l2 on l1.SSS_no = l2.SSS_no and l1.loan_date = l2.loan_date 

Vedi SQL Fiddle con Demo

Questo implementa una subquery per ottenere il max(loan_date) per ogni SSS_no . Questa sottoquery viene quindi ricollegata alla tabella sia su SSS_no che sul loan_date massimo che garantirà il risultato corretto per ogni SSS_no .

Il riferimento MySQL suggerisce diversi modi per risolvere questo problema. La più semplice è una sottoquery:

 SELECT * FROM loan l1 WHERE loan_date=(SELECT MAX(l2.loan_date) FROM loan l2 WHERE l1.sss_no = l2.sss_no); 

Dato che questo tipo di sottoquery potenzialmente ha prestazioni non buone , suggeriscono anche di usare un JOIN (essenzialmente la risposta di Mahmoud Gamal):

 SELECT l1.loan_no, l1.amount, l1.sss_no, l1.loan_date FROM loan l1 JOIN ( SELECT loan_no, MAX(loan_date) AS loan_date FROM loan GROUP BY sss_no) AS l2 ON l1.loan_date = l2.loan_date AND l1.sss_no = l2.sss_no; 

Una terza opzione è:

 SELECT l1.loan_no, l1.amount, l1.sss_no, l1.loan_date FROM loan l1 LEFT JOIN loan l2 ON l1.sss_no = l2.sss_no AND l1.loan_date < l2.loan_date WHERE l2.sss_no IS NULL; 

Il LEFT JOIN funziona sulla base del fatto che quando l1.loan_date è al suo valore massimo, c'è più tardi l2.loan_date , quindi i valori della riga l2 saranno NULL.

Tutti questi dovrebbero avere lo stesso risultato, ma probabilmente differiscono nelle prestazioni.