在自联接中只显示一次列值

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会防止联接中的重复，但这是行不通的。我还试图使用“not in”命令，但无法让它工作。我搜索了google，特别是堆栈溢出，试图找到一个答案，但是大多数类似的问题似乎都想要每一个可能的组合(如上面)，而不是将其限制在每个名称的一个外观上。

我在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);

查询(它为每个wins值生成单个耦合)：

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;