通过中间表中的条件标志合并结果的SQL N:M查询

Question

首先，如果这是重复的，对不起，我找不到对此的响应，因为这是对ORM的限制的一个奇怪的解决方案，而且我显然是SQL上的noobie

领域要求：

• 一个旅必须由一名使用者(政委一名)和一名和唯一一名助手(1:1)组成。
• 一个用户只能是一个旅的一部分(1:1)

CREATE TABLE Users
(
   id SERIAL PRIMARY KEY,
   username VARCHAR(100) NOT NULL UNIQUE,
   password VARCHAR(100) NOT NULL
);

CREATE TABLE Brigades
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL
);

-- N:M relationship with a flag inside which determine if that user is a commissar or not
CREATE TABLE Brigade_User
(
   brigade_id INT NOT NULL REFERENCES Brigades(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
   user_id INT NOT NULL REFERENCES Users(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
   is_commissar BOOLEAN NOT NULL
   PRIMARY KEY(brigade_id, user_id)
);

理想情况下，由于关系为1:1，可以删除Brigade_User中间表，而可以创建一个带有两个外键的旅表(-- 柴油锈蚀ORM不支持它，所以我想我是耦合到第一种方法了)

CREATE TABLE Brigades
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL
   -- 1:1
   commisar_id INT NOT NULL REFERENCES Users(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
   -- 1:1
   assistant_id INT NOT NULL REFERENCES Users(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE
);

一个例子..。

> SELECT * FROM brigade_user LEFT JOIN brigades ON brigade_user.brigade_id = brigades.id;

 brigade_id | user_id | is_commissar | id |       name
------------+---------+--------------+----+------------------
          1 |       1 | t            |  1 | Patrulla gatuna
          1 |       2 | f            |  1 | Patrulla gatuna
          2 |       3 | t            |  2 | Patrulla perruna
          2 |       4 | f            |  2 | Patrulla perruna
          3 |       6 | t            |  3 | Patrulla canina
          3 |       5 | f            |  3 | Patrulla canina

(4 rows)

是否可以创建一个返回这样一个表的查询？

brigade_id | commissar_id | assistant_id |        name
-----------+--------------+--------------+--------------------
         1 |       1      |      2       | Patrulla gatuna
         2 |       3      |      4       | Patrulla perruna
         3 |       6      |      5       | Patrulla canina

确保每两行都被合并为一列(记住，一个旅是由一个小卖部组成的，还有一个助手)，这取决于旗。

这个模型是否可以改进(考虑到引用同一个表的多个外键的局限性，讨论了这里)

Answer 1

尝试以下几点：

with cte as
(
  SELECT A.brigade_id,A.user_id,A.is_commissar,B.name 
  FROM brigade_user A LEFT JOIN brigades B ON A.brigade_id = B.id
)
select C1.brigade_id, C1.user_id as commissar_id , C2.user_id as assistant_id, C1.name from 
cte C1 left join cte C2
on C1.brigade_id=C2.brigade_id
and C1.user_id<>C2.user_id
where C1.is_commissar=true

请参阅来自这里的演示。