下面，我查询了排行榜中玩家的排名表。
玩家信息(包括pseudonym(存储在player表中；比赛日"；(由edition_id标识(存储在players_rankings中，因为比赛是线性的(没有积分系统，所以排名不能用数学方法计算(。每个固定装置的信息存储在set中(sw表示定位球获胜，而sl表示定位盘失利(。
SELECT
players_rankings.rank, players_rankings.change, player.pseudonym,
SUM(tot.sw) AS sw,
SUM(tot.sl) AS sl
FROM players_rankings, player, (
SELECT
player1_id AS player_id,
CASE
WHEN score1 > score2 THEN 1 ELSE 0
END AS sw,
CASE
WHEN score1 < score2 THEN 1 ELSE 0
END AS sl
FROM set WHERE edition_id = 1
UNION ALL
SELECT
player2_id,
CASE
WHEN score1 < score2 THEN 1 ELSE 0
END,
CASE
WHEN score1 > score2 THEN 1 ELSE 0
END
FROM set WHERE edition_id = 1
) AS tot
WHERE
players_rankings.edition_id = 1 AND
tot.player_id = players_rankings.player_id AND
players_rankings.player_id = player.id
GROUP BY 1, 2, 3
UNION
SELECT players_rankings.rank, players_rankings.change, player.pseudonym, 0, 0
FROM players_rankings, player
WHERE
players_rankings.edition_id = 1 AND
players_rankings.player_id = player.id
ORDER BY 1;
产生以下结果：
-----+--------+---------------+----+----+
rank | change | pseudonym | sw | sl |
-----+--------+---------------+----+----+
1 | 0 | Player One | 1 | 0 |
-----+--------+---------------+----+----+
1 | 0 | Player One | 0 | 0 |
-----+--------+---------------+----+----+
2 | 0 | Player Two | 0 | 0 |
-----+--------+---------------+----+----+
3 | 2 | Player Three | 1 | 0 |
-----+--------+---------------+----+----+
3 | 2 | Player Three | 0 | 0 |
-----+--------+---------------+----+----+
4 | -1 | Player Four | 0 | 1 |
-----+--------+---------------+----+----+
4 | -1 | Player Four | 0 | 0 |
-----+--------+---------------+----+----+
5 | -1 | Player Five | 1 | 0 |
-----+--------+---------------+----+----+
5 | -1 | Player Five | 0 | 0 |
-----+--------+---------------+----+----+
6 | 3 | Player Six | 0 | 0 |
-----+--------+---------------+----+----+
6 | 3 | Player Six | 1 | 0 |
-----+--------+---------------+----+----+
7 | -1 | Player Seven | 0 | 0 |
-----+--------+---------------+----+----+
7 | -1 | Player Seven | 0 | 1 |
-----+--------+---------------+----+----+
8 | -1 | Player Eight | 0 | 0 |
-----+--------+---------------+----+----+
8 | -1 | Player Eight | 0 | 1 |
-----+--------+---------------+----+----+
9 | -1 | Player Nine | 0 | 0 |
-----+--------+---------------+----+----+
10 | 0 | Player Ten | 0 | 1 |
-----+--------+---------------+----+----+
10 | 0 | Player Ten | 0 | 0 |
-----+--------+---------------+----+----+
11 | 0 | Player Eleven | 0 | 0 |
-----+--------+---------------+----+----+
12 | 0 | Player Twelve | 0 | 0 |
-----+--------+---------------+----+----+
我在UNION之后查询的目标是只获得第一个"游戏"中没有的注册玩家；比赛日"；(players_rankings.edition_id = 1，即玩家二、九、十一和十二(，但我尝试了不同的方法来实现这一点，包括不同的JOINs。因此，我回到绘图板上，使用前面提到的查询，用上面显示的重复值重新开始。以下是所需的结果：
-----+--------+---------------+----+----+
rank | change | pseudonym | sw | sl |
-----+--------+---------------+----+----+
1 | 0 | Player One | 1 | 0 |
-----+--------+---------------+----+----+
2 | 0 | Player Two | 0 | 0 |
-----+--------+---------------+----+----+
3 | 2 | Player Three | 1 | 0 |
-----+--------+---------------+----+----+
4 | -1 | Player Four | 0 | 1 |
-----+--------+---------------+----+----+
5 | -1 | Player Five | 1 | 0 |
-----+--------+---------------+----+----+
6 | 3 | Player Six | 1 | 0 |
-----+--------+---------------+----+----+
7 | -1 | Player Seven | 0 | 1 |
-----+--------+---------------+----+----+
8 | -1 | Player Eight | 0 | 1 |
-----+--------+---------------+----+----+
9 | -1 | Player Nine | 0 | 0 |
-----+--------+---------------+----+----+
10 | 0 | Player Ten | 0 | 1 |
-----+--------+---------------+----+----+
11 | 0 | Player Eleven | 0 | 0 |
-----+--------+---------------+----+----+
12 | 0 | Player Twelve | 0 | 0 |
-----+--------+---------------+----+----+
我应该如何实现这一点？
使用窗口函数ROW_NUMBER()和partiton by rank并使用case statement进行排序。
使用Row_Number函数，对具有相同秩的行进行分组，然后，基于该行具有条件sw = 1 OR sl = 1的事实，1的值被包括在排序中，否则，值0将按降序排序。
事实上，Row_Number函数根据相同的秩对行进行编号，并且在主查询中，将提取编号为第一的行。
SELECT rank,change,pseudonym,sw,sl
FROM
(SELECT *,
ROW_NUMBER() OVER(PARTITION BY rank ORDER BY CASE WHEN sw = 1 OR sl = 1 THEN 1 ELSE 0 END DESC) AS num
FROM
(SELECT
players_rankings.rank, players_rankings.change, player.pseudonym,
SUM(tot.sw) AS sw,
SUM(tot.sl) AS sl
FROM players_rankings, player, (
SELECT
player1_id AS player_id,
CASE
WHEN score1 > score2 THEN 1 ELSE 0
END AS sw,
CASE
WHEN score1 < score2 THEN 1 ELSE 0
END AS sl
FROM set WHERE edition_id = 1
UNION ALL
SELECT
player2_id,
CASE
WHEN score1 < score2 THEN 1 ELSE 0
END,
CASE
WHEN score1 > score2 THEN 1 ELSE 0
END
FROM set WHERE edition_id = 1
) AS tot
WHERE
players_rankings.edition_id = 1 AND
tot.player_id = players_rankings.player_id AND
players_rankings.player_id = player.id
GROUP BY 1, 2, 3
UNION
SELECT players_rankings.rank, players_rankings.change, player.pseudonym, 0, 0
FROM players_rankings, player
WHERE
players_rankings.edition_id = 1 AND
players_rankings.player_id = player.id) T) T
WHERE num = 1
ORDER BY 1;
数据库中的演示<gt；小提琴
注意：我早上的咖啡还没喝完。。。如果我正确理解你的问题，以下(未经测试(方法可以奏效：
WITH pr AS (
SELECT players_rankings.player_id,
players_rankings.rank,
players_rankings.change,
player.pseudonym
FROM players_rankings
JOIN player
ON ( players_rankings.player_id = player.id )
WHERE players_rankings.edition_id = 1
),
tot AS (
SELECT t.player_id,
sum ( t.sw ) AS sw,
sum ( t.sl ) AS sl
FROM (
SELECT player1_id AS player_id,
CASE
WHEN score1 > score2 THEN 1
ELSE 0
END AS sw,
CASE
WHEN score1 < score2 THEN 1
ELSE 0
END AS sl
FROM SET
WHERE edition_id = 1
UNION ALL
SELECT player2_id,
CASE
WHEN score1 < score2 THEN 1
ELSE 0
END,
CASE
WHEN score1 > score2 THEN 1
ELSE 0
END
FROM SET
WHERE edition_id = 1
) AS t
GROUP BY t.player_id
)
SELECT pr.rank,
pr.change,
pr.pseudonym,
0 AS sw,
0 AS sl
FROM pr
FULL OUTER JOIN tot
ON ( pr.player_id = tot.player_id )
WHERE tot.pseudonym IS NULL
ORDER BY 1 ;
编辑修复完全外部联接中的列