sql - 无法显示每个玩家赢得的比赛、进行的比赛和抽取的比赛

Question

CREATE TABLE matches (
  match_id BIGSERIAL PRIMARY KEY,
  tournamentid INTEGER,
  player1_id INTEGER,
  player2_id INTEGER CHECK (player1_id < player2_id),
  result INTEGER CHECK (result IN (0, 1, 2)),
  FOREIGN KEY(tournamentid, player1_id) REFERENCES enroll(tournament, player_id),
  FOREIGN KEY(tournamentid, player2_id) REFERENCES enroll(tournament, player_id),
  UNIQUE(tournamentid, player1_id, player2_id)
);   

CREATE VIEW player_standings AS (
SELECT tournaments.tournament_id,
     tournaments.tournament_name,
     enroll.player_id,
     players.name,
     CASE
          WHEN matches.result = 1 THEN COUNT(matches.player1_id)
          WHEN matches.result = 2 THEN COUNT(matches.player2_id)
          END AS wins,

     COUNT(enroll.player_id IN (matches.player1_id, matches.player2_id)) AS match_played

FROM players
  INNER JOIN enroll ON enroll.player_id = players.id
  INNER JOIN tournaments ON tournaments.tournament_id = enroll.tournament
  LEFT JOIN matches ON (matches.player1_id = enroll.player_id) or
       (matches.player2_id = enroll.player_id)

GROUP BY tournaments.tournament_id, tournaments.tournament_name,   
    enroll.player_id, players.name, matches.result
ORDER BY tournaments.tournament_id, wins DESC
);

我似乎无法正确获得赢得的比赛数量。我认为问题与匹配表的左连接有关。

想法是从 matches 表中的 result 列中读取结果被解释为:

0 = draw
1 = player1 won
2 = player2 won 

使用当前架构，我可以正确获取每位玩家的比赛次数，但无法获得比赛次数。

如果可能的话，我还希望在不必拆分为多个 View 或表格的情况下添加并列比赛的数量。有什么建议吗？

Answer 1

由于您没有包含所有架构，我做了一些有根据的猜测并提出了我认为应该可行的观点。我包括了一些额外的失败和平局计数，而且我发现使用 View 中显示的所有结果更容易验证结果。

CREATE VIEW player_standings AS (
SELECT 
      tournaments.tournament_id as t_id
    , tournaments.tournament_name
    , enroll.player_id
    , players.name
    , COUNT(
       CASE
        WHEN enroll.player_id = matches.player1_id AND matches.result = 1 THEN 1 
        WHEN enroll.player_id = matches.player2_id AND matches.result = 2 THEN 1 
       END) AS wins
    , COUNT(
       CASE
        WHEN enroll.player_id = matches.player1_id AND matches.result = 2 THEN 1 
        WHEN enroll.player_id = matches.player2_id AND matches.result = 1 THEN 1 
       END) AS losses
    , COUNT(CASE WHEN matches.result = 0 THEN 1 END) AS draws
    , COUNT(match_id) AS matches_played

FROM players
INNER JOIN enroll ON enroll.player_id = players.id
INNER JOIN tournaments ON tournaments.tournament_id = enroll.tournament
LEFT JOIN matches ON matches.tournamentid = tournaments.tournament_id 
                 AND enroll.player_id IN (matches.player1_id, matches.player2_id)
GROUP BY 
  tournaments.tournament_id, 
  tournaments.tournament_name, 
  enroll.player_id, 
  players.name
ORDER BY 
  tournaments.tournament_id, 
  wins DESC, 
  matches_played DESC
);

这是我创建的 SQL Fiddle 的 Markdown 输出:

SQL Fiddle

PostgreSQL 9.3 架构设置:

create table players (
  id int primary key, 
  name varchar(20)
);

insert into players values 
(1, 'Player 1'),(2, 'Player 2'),
(3, 'Player 3'),(4, 'Player 4'),(5, 'Player 5');

create table tournaments (
  tournament_id int primary key, 
  tournament_name varchar(20)
);

insert into tournaments values (1, 'Tournament 1'),(2, 'Tournament 2');

create table enroll (
  tournament int, 
  player_id int, 
  primary key (tournament, player_id),
  foreign key (tournament) references tournaments(tournament_id),
  foreign key (player_id) references players(id)
);

insert into enroll values 
(1,1),(1,2),(1,3),(1,4),(1,5),
(2,1),(2,2),(2,3),(2,4),(2,5);

CREATE TABLE matches (
  match_id bigserial PRIMARY KEY, 
  tournamentid INTEGER, 
  player1_id INTEGER, 
  player2_id INTEGER CHECK (player1_id < player2_id), 
  result INTEGER CHECK (result IN (0, 1, 2)),
  FOREIGN KEY(tournamentid, player1_id) REFERENCES enroll(tournament, player_id),
  FOREIGN KEY(tournamentid, player2_id) REFERENCES enroll(tournament, player_id),
  UNIQUE(tournamentid, player1_id, player2_id)
);

insert into matches (tournamentid, player1_id, player2_id, result) values 
 (1, 1, 2, 1) -- 1 win 2 loss
,(1, 1, 3, 1) -- 1 win 3 loss
,(1, 2, 3, 2) -- 2 win 2 loss
,(1, 1, 5, 1) -- 1 win 5 loss

,(2, 2, 4, 0) -- 2 draw 4 draw
,(2, 1, 2, 1) -- 1 win 2 loss
,(2, 3, 4, 2) -- 4 win 3 loss
;

CREATE VIEW player_standings AS (
SELECT 
      tournaments.tournament_id as t_id
    , tournaments.tournament_name
    , enroll.player_id
    , players.name
    , COUNT(
       CASE
        WHEN enroll.player_id = matches.player1_id AND matches.result = 1 THEN 1 
        WHEN enroll.player_id = matches.player2_id AND matches.result = 2 THEN 1 
       END) AS wins
    , COUNT(
       CASE
        WHEN enroll.player_id = matches.player1_id AND matches.result = 2 THEN 1 
        WHEN enroll.player_id = matches.player2_id AND matches.result = 1 THEN 1 
       END) AS losses
    , COUNT(CASE WHEN matches.result = 0 THEN 1 END) AS draws
    , COUNT(match_id) AS matches_played

FROM players
INNER JOIN enroll ON enroll.player_id = players.id
INNER JOIN tournaments ON tournaments.tournament_id = enroll.tournament
LEFT JOIN matches ON matches.tournamentid = tournaments.tournament_id 
                 AND enroll.player_id IN (matches.player1_id, matches.player2_id)
GROUP BY 
  tournaments.tournament_id, 
  tournaments.tournament_name, 
  enroll.player_id, 
  players.name
ORDER BY 
  tournaments.tournament_id, 
  wins DESC, 
  matches_played DESC
);

查询 1:

select * from player_standings

Results :

| t_id | tournament_name | player_id |     name | wins | losses | draws | matches_played |
|------|-----------------|-----------|----------|------|--------|-------|----------------|
|    1 |    Tournament 1 |         1 | Player 1 |    3 |      0 |     0 |              3 |
|    1 |    Tournament 1 |         3 | Player 3 |    1 |      1 |     0 |              2 |
|    1 |    Tournament 1 |         2 | Player 2 |    0 |      2 |     0 |              2 |
|    1 |    Tournament 1 |         5 | Player 5 |    0 |      1 |     0 |              1 |
|    1 |    Tournament 1 |         4 | Player 4 |    0 |      0 |     0 |              0 |
|    2 |    Tournament 2 |         4 | Player 4 |    1 |      0 |     1 |              2 |
|    2 |    Tournament 2 |         1 | Player 1 |    1 |      0 |     0 |              1 |
|    2 |    Tournament 2 |         2 | Player 2 |    0 |      1 |     1 |              2 |
|    2 |    Tournament 2 |         3 | Player 3 |    0 |      1 |     0 |              1 |
|    2 |    Tournament 2 |         5 | Player 5 |    0 |      0 |     0 |              0 |