使列值仅出现一次自我加入

Question

这是与带有Udacity的后端开发人员课程有关的问题。

我正在尝试创建一个系统，以根据他们的胜利配对国际象棋球员。我目前有8个球员的胜利数量相同，请参阅：

player_id | name | wins | matches -----------+-------------------+------+--------- 248 | Twilight Sparkle | 0 | 0 249 | Fluttershy | 0 | 0 250 | Applejack | 0 | 0 251 | Pinkie Pie | 0 | 0 252 | Rarity | 0 | 0 253 | Rainbow Dash | 0 | 0 254 | Princess Celestia | 0 | 0 255 | Princess Luna | 0 | 0 (8 rows)

这应该导致4个配对，应该看起来像这样：

player_id | name | player_id | name -----------+-------------------+-----------+------------------- 248 | Twilight Sparkle | 249 | Fluttershy 250 | Applejack | 251 | Pinkie Pie 252 | Rarity | 253 | Rainbow Dash 254 | Princess Celestia | 255 | Princess Luna

但是我的输出如下：

player_id |       name        | player_id |       name
-----------+-------------------+-----------+-------------------
       248 | Twilight Sparkle  |       249 | Fluttershy
       248 | Twilight Sparkle  |       250 | Applejack
       248 | Twilight Sparkle  |       251 | Pinkie Pie
       248 | Twilight Sparkle  |       252 | Rarity
       248 | Twilight Sparkle  |       253 | Rainbow Dash
       248 | Twilight Sparkle  |       254 | Princess Celestia
       248 | Twilight Sparkle  |       255 | Princess Luna
       249 | Fluttershy        |       250 | Applejack
       249 | Fluttershy        |       251 | Pinkie Pie
       249 | Fluttershy        |       252 | Rarity
       249 | Fluttershy        |       253 | Rainbow Dash
       249 | Fluttershy        |       254 | Princess Celestia
       249 | Fluttershy        |       255 | Princess Luna
       250 | Applejack         |       251 | Pinkie Pie
       250 | Applejack         |       252 | Rarity
       250 | Applejack         |       253 | Rainbow Dash
       250 | Applejack         |       254 | Princess Celestia
       250 | Applejack         |       255 | Princess Luna
       251 | Pinkie Pie        |       252 | Rarity
       251 | Pinkie Pie        |       253 | Rainbow Dash
       251 | Pinkie Pie        |       254 | Princess Celestia
       251 | Pinkie Pie        |       255 | Princess Luna
       252 | Rarity            |       253 | Rainbow Dash
       252 | Rarity            |       254 | Princess Celestia
       252 | Rarity            |       255 | Princess Luna
       253 | Rainbow Dash      |       254 | Princess Celestia
       253 | Rainbow Dash      |       255 | Princess Luna
       254 | Princess Celestia |       255 | Princess Luna
(28 rows)

您可以看到，这将输出28个配对。我需要限制代码以允许每个名称仅出现一次，无论是在第一组player_id/name列中还是第二组中。

我认为在"排名"表中制作player_id会阻止连接中的重复项，但这无效。我还尝试使用"不"命令，无法使其起作用。我已经搜索了Google，特别是Stackoverflow尝试找到答案，但是大多数类似的问题似乎都想要每种可能的组合(如上所述(，而不是仅限于每个名称的一个外观。

我在Python和Postgresql工作。我的代码如下：

select a.player_id, a.name, b.player_id, b.name from standings as a, 
standings as b
where a.wins = b.wins
and a.player_id < b.player_id
order by a.wins);

站立表的定义如下：

Table "public.standings" Column | Type | Modifiers -----------+---------+--------------------------------------------------------------- player_id | integer | not null default nextval('standings_player_id_seq'::regclass) name | text | wins | integer | default 0 matches | integer | default 0 Indexes: "standings_pkey" PRIMARY KEY, btree (player_id) Foreign-key constraints: "standings_player_id_fkey" FOREIGN KEY (player_id) REFERENCES players(player_id)

Answer 1

如果我理解您的问题，您可以尝试此问题(暂时它仅适用于偶数名称(：示例数据：

INSERT INTO TS VALUES (248, 'Twilight');
INSERT INTO TS VALUES (249, 'Flutter');
INSERT INTO TS VALUES (250, 'Apple');
INSERT INTO TS VALUES (251, 'Pinkie');
INSERT INTO TS VALUES (252, 'Rarity');
INSERT INTO TS VALUES (253, 'Rainbow');
INSERT INTO TS VALUES (254, 'Princess C',1);
INSERT INTO TS VALUES (255, 'Princess L',1);
INSERT INTO TS VALUES (278, 'Martins',1);
INSERT INTO TS VALUES (290, 'Karl L',1);

查询(它为每个赢的值生成一对夫妇(：

WITH TTS AS (SELECT *
, ROW_NUMBER() OVER (PARTITION BY WINS ORDER BY PLAYER_ID) AS RN
FROM TS)
SELECT A.WINS, A.PLAYER_ID
, A.NAME
, B.PLAYER_ID, B.NAME 
FROM TTS A
INNER JOIN TTS B ON  A.WINS = B.WINS AND A.RN+1=B.RN
WHERE A.RN %2<>0
ORDER BY A.WINS;

输出：

+------+-----------+------------+-----------+------------+
| wins | player_id |    name    | player_id |    name    |
+------+-----------+------------+-----------+------------+
|    0 |       248 | Twilight   |       249 | Flutter    |
|    0 |       250 | Apple      |       251 | Pinkie     |
|    0 |       252 | Rarity     |       253 | Rainbow    |
|    1 |       254 | Princess C |       255 | Princess L |
|    1 |       278 | Martins    |       290 | Karl L     |
+------+-----------+------------+-----------+------------+

Answer 2

对我的胜利比较对我没有意义，此外，当您不需要结果时，通过获胜的顺序。快速而肮脏的解决方案，但应符合您的要求：

SELECT a.player_id, a.name, b.player_id + 1, b.name
FROM standings as a 
INNER JOIN standings AS b
     ON abs(a.player_id - b.player_id) <= 1
     AND a.name = b.name
WHERE a.wins = b.wins
ORDER BY a.player_id;