时间戳

首页 > 解决方案 > 如何在 Postgres 中为多对多关系构建 SQL 查询选择不相关的项目？

sql - 如何在 Postgres 中为多对多关系构建 SQL 查询选择不相关的项目？

问题描述

我有具有多对多关系的编码器和编程语言实体。数据库结构如下：

coder
  - id (primary key)
  - name

proglang
  - id (primary key)
  - name

coder_to_proglang
  - id (primary key)
  - coder_id (foreign key for coder table)
  - proglang_id (foreign key for proglang table)

问题是如何选择特定编码人员不知道的编程语言？

我最好的 SQL 查询是：

select p.*, c.id as coder_id
from "proglang" as "p"
         left join "coder_to_proglang" as "j" on "p"."id" = "j"."proglang_id"
         left join "coder" as "c" on "c"."id" = "j"."coder_id"
where j.id is null or c.id != :coderId

我是这样想的：未知语言是未通过联结表与编码器链接的语言，因此联结表 ID 可以是NULL，或者该语言可以与不同的编码器有链接。但这不起作用，因为其他编码员可以知道给定编码员知道的相同语言。

我还怀疑我们可以使用一些连接来实现从一组所有现有语言中减去一组已知语言，但无法弄清楚如何在 SQL 中描述这一点。

标签： sqlpostgresqljoinselectmany-to-many

解决方案

创建所有语言（使用cross join）和代码的组合；然后过滤掉那些存在的：

select c.name as coder, p.name as language
from proglang p cross join
     coder c left join
     coder_to_proglang cp
     on cp.proglang_id = p.id and cp.coder_id = c.id
where cp.coder_id is null;

推荐阅读