Select in ordine per numero di record

Tommy03

Utente Attivo
6 Giu 2018
435
45
28
17
Bassano del Grappa (VI)
Salve, ho una tabella "sfide" con questi campi:
-sfidante (INT, contiene l'id dell'utente che ha lanciato la sfida)
-sfidato (INT, contiene l'id dell'utente che ha accettato la sfida)
-vincente (INT, contiene l'id dell'utente che ha vinto la sfida)

Ciò che devo fare pensavo fosse banale ma per me non lo è. In pratica vorrei per un determinato utente che visita il sito (con ID = x) che vedesse l'avversario con cui ha avuto il maggior numero di vittorie, il problema è che x può essere sia "sfidante" che "sfidato". Qualcuno sa aiutarmi?
P.S. se serve c'è anche un'altra tabella ("users") che contiene gli id di tutti gli utenti.
Grazie
 

IClaude

Nuovo Utente
30 Mag 2020
9
0
1
credo che tu debba fare select count di select count ... e così via ... select nidificate ...
 

marino51

Utente Attivo
28 Feb 2013
2.912
162
63
Lombardia
non so se ho capito bene, provo a darti un suggerimento come costruire la query,

"utente che visita il sito (con ID = x) che vedesse l'avversario con cui ha avuto il maggior numero di vittorie"

tutti gli avversari li chiamiamo y per comodità,

ora selezioniamo i record con sfidante = x (alias X) e sfidato (alias Y) e inseriamoci un CASE WHEN (ricordi ?)
ovvero se sfidante = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

UNION

ora selezioniamo i record con sfidato = x (alias X) e sfidante (alias Y) ed inseriamoci il CASE WHEN
ovvero se sfidato = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

a questo punto abbiamo un set con X = sfidante/sfidato, Y = sfidato/sfidante e risultato = 1 / 0 se X ha vinto o perso

select su questo set di X, Y e somma(risultato) raggruppato per X e Y e ordinato per la somma decrescente

dovrebbe essere tutto,
 
Ultima modifica:

Tommy03

Utente Attivo
6 Giu 2018
435
45
28
17
Bassano del Grappa (VI)
Ciao Marino,
prima di tutto ti illustro i record che ci sono dentro alla tabella:
tab1.png

Quindi per l'utente con id=78593169600496 ci sono 6 vittorie (con 4 avversari diversi) e 2 pareggi (indicati con 111)

Ho provato con questa query:
PHP:
SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
UNION 
SELECT sfidato as x,sfidante as y, CASE WHEN sfidato = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidato = 78593169600496
E mi restituisce così:
tab2.png

Quindi per i pareggi mi da correttamente 0, per le vittorie mette 1 per ciascuno utente, anche ai due avversari con cui ha vinto 2 volte.

ora selezioniamo i record con sfidante = x (alias X) e sfidato (alias Y) e inseriamoci un CASE WHEN (ricordi ?)
ovvero se sfidante = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

UNION

ora selezioniamo i record con sfidato = x (alias X) e sfidante (alias Y) ed inseriamoci il CASE WHEN
ovvero se sfidato = vincente restituisce 1 altrimenti 0 (alias RISULTATO)
Fino a qui quindi ho capito, ma non mi viene la parte finale.
Come posso procedere?
Grazie ancora
 

Tommy03

Utente Attivo
6 Giu 2018
435
45
28
17
Bassano del Grappa (VI)
Aggiornamento, nel frattempo penso di essere riuscito a fare la parte finale che mi hai suggerito, raggruppando per avversario e sommando le vittorie, così:
PHP:
SELECT y,SUM(vittoria) FROM
(SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
UNION
SELECT sfidato as x,sfidante as y, CASE WHEN sfidato = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidato = 78593169600496)
AS z GROUP BY y
E lo fa correttamente, però rimane sempre il problema che per la query del post precedente i risultati "doppi" non me li considera, e quindi vedo solo una vittoria anche contro gli avversari con cui ha vinto 2 volte.

Inoltre, tornando alla questione precedente (che mi riporta solo una riga per ogni avversario con cui ha vinto 2 volte) ho visto che se uso questa query (senza UNION, soltanto dove sfidante = x) me li prende tutti correttamente:
PHP:
SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
Veramente non capisco perché quando faccio la UNION mi elimina i duplicati
 
Ultima modifica:

Tommy03

Utente Attivo
6 Giu 2018
435
45
28
17
Bassano del Grappa (VI)
RISOLTO!!! Grazie mille marino51, ho tentato (a caso) a mettere UNION ALL al posto di UNION e funziona.
Pubblico la query totale se dovesse servire a qualcuno:
PHP:
SELECT y,SUM(vittoria) AS num_vittorie FROM
(SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
UNION ALL
SELECT sfidato as x,sfidante as y, CASE WHEN sfidato = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidato = 78593169600496)
AS z GROUP BY y ORDER BY num_vittorie DESC
Senza il tuo aiuto non ce l'avrei mai fatta, grazie ancora!