mysql - 用 SQL 证明关系演算

Question

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

哪位作者与每位作者共同撰写了至少 1 篇论文(无聚合函数)

鉴于此架构，

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
));