sql - SQL 从满足预定义条件的表中选择成对组合

Question

我有一个类似于以下内容的 PostgreSQL 表：

+------+----------+----------+------------+
|  id  | group_id |   role   | subject_id |
+------+----------+----------+------------+
| A-L1 | A        | leader   |          1 |
| A-F2 | A        | follower |          2 |
| A-F3 | A        | follower |          3 |
| B-L4 | B        | leader   |          4 |
| B-F5 | B        | follower |          5 |
| B-F6 | B        | follower |          6 |
| B-F7 | B        | follower |          7 |
| C-L8 | C        | leader   |          8 |
| C-F9 | C        | follower |          9 |
+------+----------+----------+------------+

我想从这个表中选择成对组合，但只在每个组内，并且只选择那些涉及“领导”角色的成对组合。例如。结果应如下所示：

+---------+-----------+-------------+
| pair_id | leader_id | follower_id |
+---------+-----------+-------------+
| A-L1-F2 | A-L1      | A-F2        |
| A-L1-F3 | A-L1      | A-F3        |
| B-L4-F5 | B-L4      | B-F5        |
| B-L4-F6 | B-L4      | B-F6        |
| B-L4-F7 | B-L4      | B-F7        |
| C-L8-F9 | C-L8      | C-F9        |
+---------+-----------+-------------+

由于我将多次进行此查询，因此我想将其转换为视图。关于我可以使用的 SQL 代码的任何建议？在这一点上我还没有尝试过任何东西，因为我不确定从哪里开始，并且刚刚开始使用 SQL。

标签： sqldatabasepostgresql

Answer 1

考虑使用与相同group_id但不同角色对齐的自联接，最后两个表与id和相同角色对齐：

SELECT t1.id || Replace(t2.id, t2.group_id, '') AS pair_id, 
       t1.id AS leader_id, 
       t3.id AS follower_id
FROM pgTable t1
INNER JOIN pgTable t2 ON t1.group_id = t2.group_id
INNER JOIN pgTable t3 ON t1.group_id = t3.group_id AND t2.id = t3.id
WHERE t1.role = 'leader' AND t2.role = 'follower' AND t3.role = 'follower';

Rextester 演示