|
|
|
|
Strumenti |
10-07-2008, 15:49 | #1 |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
[SQL] - [MySQL] Gestione torneo pallavolo - problema classifiche
In breve, al mio paese hanno organizzato un torneo estivo di pallavolo femminile e io mi sono occupato di fare qualcosa online, per cui ho installato wordpress e alcune pagine php scritte da me per la visualizzazione dei risultati e delle classifiche + front-end di inserimento risultati per me.
Ci sono due categorie di età che sono però gestite assieme, sulle stesse tabelle. Il sistema usa la libreria mysqli per interfacciare php con MySQL. Funziona così: Ho essenzialmente 3 tabelle: - Categorie. è molto semplice associa un nome completo di categoria ("Giovani", "Ragazze") al suo codice breve, un carattere ('g', 'r'). Così diventa più veloce per il sistema assegnare delle squadre a delle categorie. CODICE SQL - Squadre. In pratica assegna un codice (numero intero univoco) ad ogni squadra, il nome completo e il codice di categoria. Gli id vengono attualmente usati nella tabella delle partite. CODICE SQL - Partite. Contiene l'elenco degli incontri giocati oppure ancora da giocare. Sono definiti gli id delle 2 squadre, data e ora (tramite un timestamp Unix) e, dopo aver registrato i risultati, i parziali di ogni set (parz_{0-3}{A|B}). Nonostante nella definizione SQL contenga anche il numero dei set e l'id del vincitore, queste non vengono utilizzate perché sono calcolate all'occorrenza dal sistema, come scritto nelle note del link al codice. CODICE SQL Per visualizzare la classifica utilizzo delle view. - La prima, calcola i risultati (ovvero set vinti, set persi, id vincitore, totale punti fatti, totale punti subiti) per ogni partita, leggendo i parziali dei set. CODICE SQL - La seconda, indispensabile per calcolare la classifica, prende tutte le partite (più risultati) e le inserisce 2 volte, scambiando le 2 squadre. Così posso avere tutte le partite "viste" da tutte le squadre. Così, per ogni squadra, posso vedere tutti i risultati conseguiti. Spero di essere stato chiaro, mi ci è voluto un po' per elaborare il sistema. CODICE SQL - La terza è la classifica vera e propria. Per ogni squadra (quindi un GROUP BY) somma i set vinti, set persi, partite vinte, parite perse, punti fatti, punti subiti, ecc. CODICE SQL Ma questa view non ordina i record per cui è praticamente "inutile". Quindi mi sono messo d'impegno e ho aggiunto la clause ORDER BY. Priorità dei fattori per l'ordinamento (indicazioni di mio papà): - Set vinti (decrescente) - Partite vinte (decrescente) - Quoziente set (il rapporto set vinti/set persi) - Quoziente punti (il rapporto punti fatti/punti subiti) - Alfabetico; se tutti gli altri parametri sono uguali (caso estremo) allora si va di ordine alfabetico sul nome della squadra (quindi ascendente). Ecco il nuovo codice SQL: CODICE SQL che definisce l'ordinamente come l'ho spiegato io prima. Per cui ora è il momento di eseguire le query. Ma qui sorge un problema. phpMyAdmin riporta: Codice:
Messaggio di MySQL: Documentazione #1247 - Reference 'SetVinti' not supported (reference to group function) A dire la verità ho risolto il problema spostando la clause ORDER BY nella query di SELECT che esegui mysqli per leggere la view. Ma volevo che fosse la view a occuparsi dell'ordinamento. GRAZIE IN ANTICIPO E FATE TUTTE LE DOMANDE CHE VOLETE PS. che dite lascio il codice disponibile ai link oppure lo posto con il tag <code> ??? dite come vi sembra più comodo
__________________
Ultima modifica di malocchio : 24-07-2009 alle 11:02. |
10-07-2008, 15:55 | #2 |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
Se vi può essere utile ecco i link alle pagine web dove si possono visualizzare i risultati e classifiche:
Elenco partite disputate Classifiche
__________________
|
10-07-2008, 16:01 | #3 |
Senior Member
Iscritto dal: May 2008
Città: Provincia Torino
Messaggi: 749
|
puoi postare qui la select che non funziona? ho un problema ad andare sulle pagine che linki da qui..
__________________
Ho comprato serenamente da: Zio_Igna, gwwmas, andreabarbuscia, marczxc, turbofantasyfan |
10-07-2008, 16:16 | #4 |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
Tabella categorie
Codice:
#http://phpfi.com/330823 DELIMITER $$ DROP TABLE IF EXISTS `cats`$$ CREATE TABLE `cats` ( `id` CHAR(1) NOT NULL, `cat` VARCHAR(20) NOT NULL, PRIMARY KEY(`id`) ) ENGINE = MYISAM; $$ Codice:
#http://phpfi.com/330821 DELIMITER $$ DROP TABLE IF EXISTS `teams` $$ CREATE TABLE `teams` ( `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `cat` CHAR(1) NOT NULL, PRIMARY KEY(`id`) ) ENGINE = MYISAM $$ Codice:
#http://phpfi.com/330824 DELIMITER $$ DROP TABLE IF EXISTS `matches` $$ CREATE TABLE `matches` ( `id` smallint(5) unsigned NOT NULL auto_increment, `id_A` smallint(5) unsigned NOT NULL, `id_B` smallint(5) unsigned NOT NULL, `time` int(10) unsigned NOT NULL, `sets_A` smallint(5) unsigned default NULL, #disuso `sets_B` smallint(5) unsigned default NULL, #disuso `parz_1A` smallint(5) unsigned default NULL, `parz_1B` smallint(5) unsigned default NULL, `parz_2A` smallint(5) unsigned default NULL, `parz_2B` smallint(5) unsigned default NULL, `parz_3A` smallint(5) unsigned default NULL, `parz_3B` smallint(5) unsigned default NULL, `winner` smallint(5) unsigned default NULL, #disuso PRIMARY KEY (`id`) ) ENGINE=MyISAM $$ Codice:
#http://phpfi.com/330826 DELIMITER $$ DROP VIEW IF EXISTS `vw_match_result`$$ CREATE VIEW `vw_match_result` AS SELECT `id` AS `id`, ( (`parz_1A`>`parz_1B`)+ (`parz_2A`>`parz_2B`)+ (`parz_3A`>`parz_3B`) ) AS `sets_A`, ( (`parz_1A`<`parz_1B`)+ (`parz_2A`<`parz_2B`)+ (`parz_3A`<`parz_3B`) ) AS `sets_B`, `parz_1A`+`parz_2A`+`parz_3A` AS `points_A`, `parz_1B`+`parz_2B`+`parz_3B` AS `points_B`, IF( ((`parz_1A`>`parz_1B`)+ (`parz_2A`>`parz_2B`)+ (`parz_3A`>`parz_3B`)) > ((`parz_1A`<`parz_1B`)+ (`parz_2A`<`parz_2B`)+ (`parz_3A`<`parz_3B`)), `id_A`, IF( ((`parz_1A`>`parz_1B`)+ (`parz_2A`>`parz_2B`)+ (`parz_3A`>`parz_3B`)) < ((`parz_1A`<`parz_1B`)+ (`parz_2A`<`parz_2B`)+ (`parz_3A`<`parz_3B`)), `id_B`, NULL) ) AS `winner` FROM `matches`$$ Codice:
#http://phpfi.com/330827 DELIMITER $$ DROP VIEW IF EXISTS `teams_matches`$$ CREATE VIEW `teams_matches` AS SELECT `m`.`id_A` AS `team_id`, `t`.`cat` AS `cat`, `t`.`name` AS `Squadra`, `m`.`id` AS `match_id`, `mr`.`sets_A` AS `Set vinti`, `mr`.`sets_B` AS `Set persi`, `mr`.`points_A` AS `Punti fatti`, `mr`.`points_B` AS `Punti subiti`, `mr`.`sets_A`>`mr`.`sets_B` AS `Vinto`, `mr`.`sets_A`<`mr`.`sets_B` AS `Perso` FROM `matches` AS `m` INNER JOIN `teams` AS `t` ON `m`.`id_A`=`t`.`id` INNER JOIN `vw_match_result` AS `mr` ON `m`.`id`=`mr`.`id` UNION SELECT `m`.`id_B` AS `team_id`, `t`.`cat` AS `cat`, `t`.`name` AS `Squadra`, `m`.`id` AS `match_id`, `m`.`sets_B` AS `Set vinti`, `m`.`sets_A` AS `Set persi`, `mr`.`points_B` AS `Punti fatti`, `mr`.`points_A` AS `Punti subiti`, `m`.`sets_B`>`mr`.`sets_A` AS `Vinto`, `m`.`sets_B`<`mr`.`sets_A` AS `Perso` FROM `matches` AS `m` INNER JOIN `teams` AS `t` ON `m`.`id_B`=`t`.`id` INNER JOIN `vw_match_result` AS `mr` ON `m`.`id`=`mr`.`id` $$ Codice:
DELIMITER $$ DROP VIEW IF EXISTS `vw_chart_bysets`$$ CREATE VIEW `vw_chart_bysets` AS SELECT `t`.`id` AS `id`, `t`.`cat` AS `cat`, `t`.`name` AS `Squadra`, SUM(`tm`.`Set vinti`) AS `SetVinti`, SUM(`tm`.`Set persi`) AS `SetPersi`, SUM(`tm`.`Set vinti`)+SUM(`tm`.`Set persi`) AS `SetGiocati`, SUM(`tm`.`Vinto`) AS `PartiteVinte`, SUM(`tm`.`Perso`) AS `PartitePerse`, COUNT(`tm`.`Vinto`) AS `PartiteGiocate`, SUM(`tm`.`Punti fatti`) AS `PuntiFatti`, SUM(`tm`.`Punti subiti`) AS `PuntiSubiti`, SUM(`tm`.`Punti fatti`)+SUM(`tm`.`Punti subiti`) AS `PuntiGiocati` FROM `teams` AS `t` LEFT JOIN `teams_matches` AS `tm` ON `t`.`id`=`tm`.`team_id` GROUP BY `id` ORDER BY `SetVinti` DESC, `PartiteVinte` DESC, `SetVinti`/`SetPersi` DESC, `PuntiFatti`/`PuntiSubiti` DESC, `Squadra` ASC $$ Codice:
SELECT * FROM `vw_chart_bysets` #la vista della classifica SENZA ordinamento WHERE `cat`= ? #è uno prepared statement, sostituisco ? con il codice della categoria ('r' o 'g') ORDER BY `SetVinti` DESC, `PartiteVinte` DESC, `SetVinti`/`SetPersi` DESC, #quoziente set `PuntiFatti`/`PuntiSubiti` DESC #quoziente punti `Squadra` ASC
__________________
|
11-07-2008, 08:27 | #5 |
Senior Member
Iscritto dal: May 2008
Città: Provincia Torino
Messaggi: 749
|
ho cercvato un po in giro..pare sia un bug di mysql
dovresti prima lanciare il comando sul db: set @@sql_mode='ONLY_FULL_GROUP_BY'; poi dovrebbe darti un errore del genere: non-grouping field 'SetVinti' is used in HAVING clause e da qui in avanti non ho mica capito come si risolve http://bugs.mysql.com/bug.php?id=31797
__________________
Ho comprato serenamente da: Zio_Igna, gwwmas, andreabarbuscia, marczxc, turbofantasyfan |
11-07-2008, 09:00 | #6 | |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
Quote:
secondo... potresti postare il link anche del primo bug.. così ho le informazioni complete e capire meglio perché dà l'errore... TNX edit: ah no scusa avevo capito male il bug è uno solo... adesso guardo se ONLY_FULL_GROUP_BY mi può essere utile...
__________________
Ultima modifica di malocchio : 11-07-2008 alle 09:36. |
|
11-07-2008, 09:17 | #7 |
Senior Member
Iscritto dal: Sep 2004
Città: Prov. GE
Messaggi: 3419
|
Ma nel GROUP BY non mancano cat e name ??
|
11-07-2008, 09:44 | #8 |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
Se non è attivato ONLY_FULL_GROUP_BY non è necessario elencare tutte le colonne di tipo non group.
Ho appena provato una normale query SELECT. La cosa strana è che `SetVinti` lo posso mettere in ORDER BY perché se io scrivo una query del genere: Codice:
SELECT `t`.`id` AS `id`, `t`.`cat` AS `cat`, `t`.`name` AS `Squadra`, SUM(`tm`.`Set vinti`) AS `SetVinti`, SUM(`tm`.`Set persi`) AS `SetPersi`, SUM(`tm`.`Set vinti`)+SUM(`tm`.`Set persi`) AS `SetGiocati`, SUM(`tm`.`Vinto`) AS `PartiteVinte`, SUM(`tm`.`Perso`) AS `PartitePerse`, COUNT(`tm`.`Vinto`) AS `PartiteGiocate`, SUM(`tm`.`Punti fatti`) AS `PuntiFatti`, SUM(`tm`.`Punti subiti`) AS `PuntiSubiti`, SUM(`tm`.`Punti fatti`)+SUM(`tm`.`Punti subiti`) AS `PuntiGiocati` FROM `teams` AS `t` LEFT JOIN `teams_matches` AS `tm` ON `t`.`id`=`tm`.`team_id` GROUP BY `id`, `name`, `cat` ORDER BY `SetVinti`, `PartiteVinte`, `SetVinti` Ma se io invece del secondo SetVinti nell'order scrivo `SetVinti`/`SetPersi` allora mi dà errore sul / reference to group function. Cos'è la divisione tra interi non supportata? Spero di non aver beccato un bug perché azzanno quelli di AB sennò Notate che non c'è ambiguita di nomi colonne perché quelli delle tabelle FROM sono diversi dagli alias che ho assegnato...
__________________
|
11-07-2008, 09:57 | #9 | |
Senior Member
Iscritto dal: May 2008
Città: Provincia Torino
Messaggi: 749
|
Quote:
__________________
Ho comprato serenamente da: Zio_Igna, gwwmas, andreabarbuscia, marczxc, turbofantasyfan |
|
14-07-2008, 16:56 | #10 |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
UP..
Perché nell'ORDER mi dà errore quando uso l'operatore "/" ???
__________________
|
16-07-2008, 09:17 | #11 |
Senior Member
Iscritto dal: Feb 2007
Città: Verona
Messaggi: 1060
|
up
__________________
|
Strumenti | |
|
|
Tutti gli orari sono GMT +1. Ora sono le: 04:40.