主页 / dba / 问题 / 201802
Accepted
MightyRavendark
Asked: 2018-03-21 08:52:21 +0800 CST

MS Access M:N 关系查询：排除超过请求值的记录

  • 772

在查找与其他表具有 m:n 关系并因此在链接表中多次出现的记录时，我遇到了一个小问题。我尝试在这里用一个最小的例子来展示它：

假设我们有一个名为“books”的表，一个名为“authors”的表和一个连接两者的链接表。

数据模型

有些书由一位作者撰写，有些则由多位作者撰写。

在此处输入图像描述

正如我们所见，第一本书 (book_id = 1) 仅由一个人 (authorID = 1) 撰写，而第二本书 (book_id = 2) 由两个人 (authorID 1 和 3) 撰写。如果我们现在尝试获取作者 1 和作者 3 所写的书，我们可以这样进行：

SELECT book_id, title, year_of_publication, pagecount, cover FROM (book
INNER JOIN 
(SELECT * FROM link_author_book WHERE author = 1) AS author1 
ON author1.book = book.book_id)
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 3) AS author2
ON author2.book = book.book_id

这导致了这个： 在此处输入图像描述

我很确定我的解决方案不是最好的方法，但在这种情况下它是有效的......无论如何，当我们试图只返回作者 1 而没有其他人写的那些书时，我们有一个问题。在代码中，此选择如下所示：

SELECT book_id, title, year_of_publication, pagecount, cover FROM book
INNER JOIN 
link_author_book ON link_author_book.book = book.book_id
WHERE author = 1;

并导致此记录集：

在此处输入图像描述

虽然从技术上讲 authorID = 1 是这两本书的作者，但我真正想要的只是第一本书 (book_id = 1)。所以这是我的问题：如何排除在这种 m:n 关系中链接到的查询值多于查询值的记录？以及如何在 Access 2016 中将其作为 SQL 语句完成？提前致谢

ms-access subquery

1 个回答

  1. Best Answer

    关键是学习编写直接回答问题的查询。从购买只有一位作者（“没有其他人”）的书开始。这将需要一个聚合查询来计算每本书的作者：

    SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) = 1

    现在将结果加入其他表以获得您想要/需要的详细信息。我个人更喜欢保存此类中间查询，然后在下一个查询中按名称引用它们（因为您随后可以使用 Access 查询设计器单独编辑它们），但为了明确起见，我会将其作为子查询包含在内：

    SELECT book.title, author.lastname
  ((SELECT lab.book, first(lab.author) AS author
    FROM link_author_book AS lab
    GROUP BY lab.book
    HAVING Count(lab.author) = 1)
  AS SingleAuthor
  INNER JOIN book ON SingleAuthor.book = book.book_id)
  INNER JOIN author ON SingleAuthor.author = author.auth_id

    真的，你也可以写一个查询来回答你的其他问题，比如获取“超过查询值”的记录......

    SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) <> 1

    然后“排除”这样的记录：

    SELECT book.title, author.lastname
  ((book
  LEFT JOIN (SELECT lab.book FROM link_author_book AS lab
  GROUP BY lab.book
  HAVING Count(lab.author) <> 1)
  AS OtherAuthors
  ON book.book_id = OtherAuthors.book)
  INNER JOIN link_author_book AS lab ON book.book_id = lab.book)
  INNER JOIN author ON author.auth_id = lab.author
WHERE OtherAuthors.book Is NULL

    由于左连接，这将最初选择 [books] 中的所有记录，但随后将通过仅保留不在 [OtherAuthors] 子查询中的记录来排除具有超过 1 个作者的书籍。但是正如您所见，这是获得相同结果的一种过于复杂的方法。我只是包括这个来表明可以使用其他类型的连接和适当的条件来排除记录。这是一种有用的技术，尤其是在无法直接选择所需记录的情况下。（有时排除记录确实是最好的方法，但在这种情况下使用链接表就没有必要了。）

    • 1

相关问题