用 SQL 证明关系演算

Question

对于作业，我的任务是为这个语句编写一个关系演算查询，

哪位作者与每位作者合著了至少一篇论文（无聚合函数）

给定此模式，

Authors( auId, name)
Authoring( articleId, authorId )

我想先使用 sql 证明我的查询，但我注意到我只得到每篇文章的共同作者，而我只需要至少与其他所有作者共同撰写文章的作者。如何更改查询以获得所需的结果？谢谢。

这是我提出的查询，

select distinct authorid
from authoring as au1
where not exists(
    select *
    from authoring as ar1
    where not exists(
        select *
        from  authoring as a2
        where a2.articleid = ar1.articleid
          and a2.authorid  = au1.authorid
));
The result I am getting is
authorId
--------
1
The result should be 1,2 because authorID 2 shares a article with both authors   1 & 3
authorId
--------
1
2
Authors Table
auid | name
-----------
1    | Michael
2    | Jazmine
3    | Amanda
Authoring Table
articleId | authorId
--------------------
1         | 1
2         | 1
3         | 1
1         | 2
3         | 2
1         | 1

我在下面发布了这个问题的答案。

Answer 1

SQL 查询，用于查找与其他每位作者共同撰写至少一篇文章的所有作者。

select *
from `authors` as a1
where not exists(
    select *
    from `authors` as a2
    where a2.auid <> a1.auid
    and not exists(
        select *
        from authoring as ar1
        inner join authoring as ar2
            on ar1.articleid = ar2.articleid
        where ar1.authorid    = a1.auid
        and   ar2.authorid    = a2.auid
));