View Single Post
Old 10-07-2008, 16:16   #4
malocchio
Senior Member
 
L'Avatar di malocchio
 
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; $$
Tabella squadre
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 $$
Tabella partite
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 $$
Vista risultati partita
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`$$
Vista partite per ogni squadra
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`
$$
Vista classifica, completa di ordinamento (NON funzionante)
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
$$
Query che eseguo sulla pagina PHP:
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
__________________
malocchio č offline   Rispondi citando il messaggio o parte di esso