sql - 如何在不使用 View 的情况下在 Postgres 中制定此查询

Question

我有一个我正在尝试制定的练习查询，它也恰好非常棘手，因为练习明确告诉您不要使用 View 。它基于 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
     )

基本上，外部查询按顺序创建每对记录标签，并使用相关子查询选择每一对，该子查询搜索这两个记录标签之间是否没有任何共同版本。这部分按预期工作，但我不知道如何在不使用任何 View 或任何条件或控制结构的情况下找到相同的第三个标签。帮助。

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
     )