Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE VIEW v_matches_won_per_player AS
- SELECT t_players.id, t_players.name,
- COUNT(t_matches.id_winner) AS total_matches_won, t_matches.id_tournament
- FROM t_players
- JOIN t_matches
- ON t_players.id = t_matches.id_winner
- GROUP BY t_players.id, t_matches.id_tournament
- ORDER BY total_matches_won DESC;
- CREATE VIEW v_matches_lost_per_player AS
- SELECT t_players.id, t_players.name,
- COUNT(t_matches.id_loser) AS total_matches_lost, t_matches.id_tournament
- FROM t_players JOIN t_matches
- ON t_players.id = t_matches.id_loser
- GROUP BY t_players.id, t_matches.id_tournament
- ORDER BY total_matches_lost DESC;
- CREATE VIEW v_total_stats AS
- SELECT v_matches_won_per_player.id, v_matches_won_per_player.name,
- v_matches_won_per_player.total_matches_won AS total_matches_won,
- v_matches_lost_per_player.total_matches_lost AS total_matches_lost,
- v_matches_won_per_player.total_matches_won + v_matches_lost_per_player.total_matches_lost AS total_matches_played
- FROM v_matches_won_per_player, v_matches_lost_per_player
- WHERE v_matches_won_per_player.id = v_matches_lost_per_player.id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement