SQL:在 1:n 关系中查找不符合跨越多行的条件的条目

Question

我正在尝试优化 Akonadi 中的 SQL 查询，遇到了以下显然不容易用 SQL 解决的问题，至少对我而言:

假设以下表结构(应该在 SQLite、PostgreSQL、MySQL 中工作):

CREATE TABLE a (
  a_id INT PRIMARY KEY
);

INSERT INTO a (a_id) VALUES (1), (2), (3), (4);

CREATE TABLE b (
  b_id INT PRIMARY KEY,
  a_id INT,
  name VARCHAR(255) NOT NULL
);

INSERT INTO b (b_id, a_id, name)
       VALUES (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'asdf'),
              (4, 2, 'foo'), (5, 2, 'bar'), (6, 3, 'foo');

现在我的问题是在 a 中找到缺少表 b 中的 name 条目的条目。例如。我需要确保 a 中的每个条目至少有 name 条目 "foo" 和 "bar"在表 b 中。因此，查询应返回类似于以下内容的内容:

a_id = 3 is missing name "bar"
a_id = 4 is missing name "foo" and "bar"

由于 Akonadi 中的两个表都可能很大，因此性能至关重要。

MySQL 中的一个解决方案是:

SELECT a.a_id,
       CONCAT('|', GROUP_CONCAT(name ORDER BY NAME ASC SEPARATOR '|'), '|') as names
  FROM a
  LEFT JOIN b USING( a_id )
  GROUP BY a.a_id
  HAVING names IS NULL OR names NOT LIKE '%|bar|foo|%';

我还没有测量明天的性能，但严重怀疑它对于 a 中的数万个条目和 b 中的三倍数来说是否很快。此外，我们希望支持 SQLite 和 PostgreSQL，据我所知，GROUP_CONCAT 函数不可用。

谢谢，晚安

Answer 1

这应该适用于任何 SQL 标准 RDBMS:

SELECT 
   a.a_id, 
   Foo.b_id as Foo_Id,
   Bar.b_id as Bar_Id
FROM a
LEFT OUTER JOIN (SELECT a_id, b_id FROM b WHERE name = 'foo') as Foo ON
   a.a_id = Foo.a_id
LEFT OUTER JOIN (SELECT a_id, b_id FROM b WHERE name = 'bar') as Bar ON
   a.a_id = Bar.a_id
WHERE
   Foo.a_id IS NULL
   OR Bar.a_id IS NULL