如何在Postgres中制定此查询而不使用视图

Question

我有一个练习查询需要制定，但是这个练习明确告诉你不要使用视图，所以它也很棘手。它是基于Musicbrainz数据库模式设计的，但是只使用了以下关系，并剥离了所有未使用的属性：

label {id(PK), name}

release {id(PK), name}

-- associates a label to all its releases
release_label {id(PK), release(FK), label(FK)}

查询内容为：“列出所有没有发布任何共同发行的唱片公司对，但与同一第三个唱片公司合作发布了一个发行（显示唱片公司对的名称）”。

我已经试图理解这个查询几天了，但我只能使用同一张表的自连接来创建对，解决了前半部分（从未发布过共同发行的标签对）。

SELECT DISTINCT label_1.name, label_2.name
FROM label label_1 JOIN label label_2 ON label_1.name < label_2.name
WHERE NOT EXISTS 
     (SELECT * 
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_1.name = label_A.name AND label_2.name = label_B.name
     )

基本上，外部查询按顺序创建每对唱片标签，并使用相关子查询选择每对唱片标签，以搜索这两个唱片标签之间是否存在任何共同的发行。 这部分按预期工作，但是我不知道如何在不使用任何视图或任何条件或控制结构的情况下找到相同的第三个标签。 帮帮我。

- DefinitelyNEET

---

普通表达式是允许的吗？通过使用它们，你可以模拟视图。 - wumpz

不，你也不能使用那些。 - DefinitelyNEET

那么 SQL 函数怎么样？ - Belayer

很不幸，甚至连那些也有很多限制，只是为了增加难度而已。 - DefinitelyNEET

抱歉，没有CTE，没有注意到这个。 - Damir Sudarevic

Answer 1

考虑对label进行第三次自连接，加入两个EXISTS子句，并以它们的WHERE条件不同:

SELECT DISTINCT label_1.name, label_2.name, label_3.name
FROM label label_1
JOIN label label_2 ON label_1.name < label_2.name
JOIN label label_3 ON label_3.name <> label_1.name
                   AND label_3.name <> label_2.name
WHERE NOT EXISTS 
     (SELECT 1
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_1.name = label_A.name
        AND label_2.name = label_B.name
     )
 AND EXISTS
     (SELECT 1
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_1.name = label_A.name 
        AND label_3.name = label_B.name
     )
 AND EXISTS
     (SELECT 1
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_2.name = label_A.name 
        AND label_3.name = label_B.name
     )

- Parfait

---

谢谢，我不知道为什么之前没有想到这个。虽然它非常慢，需要在我的数据库上运行大约4分钟，但这并不重要。我的意思是，这个练习是关于不使用CTE的，但我刚刚尝试添加了一个CTE，运行时间从4分钟缩短到了90毫秒。 - DefinitelyNEET

很高兴听到这个消息并且很高兴能够帮助。是的，CTE可以防止多次运行相同的结果集，我们在此处使用不同的WHERE条件重复执行。 - Parfait

Answer 2

查询

with
q_00 as (
-- This should already be unique in release_label,
-- but the unique key is not specified in the schema
select distinct release, label
from release_label
),
q_01 as (
-- Releases and pairs of labels common to that release
-- includes pairs (l1, l2) = (l2, l1)
select a.release  as r1
     , a.label    as l1
     , b.label    as l2
from q_00 as a
join q_00 as b on a.release = b.release
              and a.label  != b.label
),
q_02 as (
-- All pairs of labels which do have a common release
-- includes pairs (l1, l2) = (l2, l1)
select distinct l1, l2
from q_01
),
q_03 as (
-- All possible pairs of labels
-- includes pairs (l1, l2) = (l2, l1)
select distinct
       a.label    as l1
     , b.label    as l2
from q_00 as a
join q_00 as b on a.label != b.label
),
q_04 as (
-- All pairs of labels which do NOT have a common release
-- includes pairs (l1, l2) = (l2, l1)
select l1, l2 from q_03
except
select l1, l2 from q_02
),
q_05 as (
-- l1 and l2 did not work together, but they
-- both worked with l3
select
       a.l1 as l1
     , a.l2 as l2
     , b.l2 as l3
from q_04 as a
join q_02 as b on b.l1 = a.l1
join q_02 as c on c.l1 = a.l2 and c.l2 = b.l2
)
-- Result with label names, no repeats (l1, l2) = (l2, l1)
-- lbl_1 and lbl_2 did not work together, but they
-- both worked with lbl_3
select distinct
       b.name as lbl_1
     , c.name as lbl_2
     , d.name as lbl_3
from q_05  as a
join label as b on b.id = a.l1
join label as c on c.id = a.l2
join label as d on d.id = a.l3
where b.name < c.name
order by lbl_1, lbl_2, lbl_3
;

测试

创建表格

create table label (
    id    int  not null
  , name  text not null

  , constraint pk_label primary key (id)
  );

create table release (
    id    int  not null
  , name  text not null

  , constraint pk_release primary key (id)
);

create table release_label (
    id       int  not null
  , release  int  not null
  , label    int  not null

  , constraint pk_rel_lbl primary key (id)

  , constraint fk1_rel_lbl foreign key (release)
                             references release (id)

  , constraint fk2_rel_lbl foreign key (label)
                             references label (id)
);

添加示例数据

insert into label (id, name)
values
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'),
(5, 'E'), (6, 'F'), (7, 'G'), (8, 'H');

insert into release (id, "name")
values
( 1, 'R01'), ( 2, 'R02'), ( 3, 'R03'), ( 4, 'R04'),
( 5, 'R05'), ( 6, 'R06'), ( 7, 'R07'), ( 8, 'R08');

insert into release_label (id, release, label)
values
-- R01 (A, F, H)
  (11, 1, 1)
, (12, 1, 6)
, (13, 1, 8)

-- R02 (B)
, (21, 2, 2)

-- R03 (C, D)
, (31, 3, 3)
, (32, 3, 4)

-- R04 (B, F)
, (41, 4, 2)
, (42, 4, 6)

-- R05 (A, F)
, (51, 5, 1)
, (52, 5, 6)

-- R06 (D, G)
, (61, 6, 4)
, (62, 6, 7)

-- R07 (A, G)
, (71, 7, 1)
, (72, 7, 7)

-- R08 (E)
, (81, 8, 5)
;

结果

 lbl_1 | lbl_2 | lbl_3
-------+-------+-------
 A     | B     | F
 A     | D     | G
 B     | H     | F
 C     | G     | D
 F     | G     | A
 G     | H     | A
(6 rows)

- Damir Sudarevic