左连接不起作用(MySQL)

发布于 2024-09-11 08:28:06 字数 709 浏览 5 评论 0原文

我有一个 Books 表,用于存储图书数据(ISBN、书名、作者等)。为了区分哪些书是彼此的版本,我有一个字段 Edition_Group_ISBN,它是该组中的任意 ISBN。

我在获取此查询时遇到问题,该查询应该提供图书数据和基于 ISBN 的其他版本数量:

   SELECT *, Editions_Count 
     FROM Books 
LEFT JOIN ((SELECT Edition_Group_ISBN, COUNT(*) AS Editions_Count 
              FROM Books 
             WHERE Edition_Group_ISBN IN (SELECT Edition_Group_ISBN 
                                            FROM Books)
             GROUP BY Edition_Group_ISBN) AS b 
       ) ON (Books.Edition_Group_ISBN = b.Edition_Group_ISBN 
           AND Books.Edition_Group_ISBN != NULL) 
    WHERE ISBN = 9780140447897

该查询提供了 9780140447897 的图书数据,但它给出了 Editions_Count AS NULL,表明LEFT JOIN 不起作用。

I have a table Books, where I store Book data (ISBN's, Titles, Authors, etc.). To tell which books are editions of each other I have a field Edition_Group_ISBN, which is an arbitrary ISBN from the group.

I'm having trouble getting this query, which is supposed to give the Book data and the number of other Editions based on the ISBN, to work:

   SELECT *, Editions_Count 
     FROM Books 
LEFT JOIN ((SELECT Edition_Group_ISBN, COUNT(*) AS Editions_Count 
              FROM Books 
             WHERE Edition_Group_ISBN IN (SELECT Edition_Group_ISBN 
                                            FROM Books)
             GROUP BY Edition_Group_ISBN) AS b 
       ) ON (Books.Edition_Group_ISBN = b.Edition_Group_ISBN 
           AND Books.Edition_Group_ISBN != NULL) 
    WHERE ISBN = 9780140447897

The query gives the book data for 9780140447897, but it gives the Editions_Count AS NULL, indicating that the LEFT JOIN isn't working.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

巴黎盛开的樱花 2024-09-18 08:28:06

尝试这个更简单的查询:

SELECT b.*, COUNT(*) AS Editions_count
FROM Books b JOIN Books g USING (Edition_Group_ISBN)
WHERE b.ISBN = 9780140447897
GROUP BY b.book_id;

我认为你让问题变得比你需要的更加困难。您的原始查询充满了令人困惑的子查询,至少其中一个是完全多余的。


回复您的评论:

是的,通过这种方式将 ISBN 与多个值进行比较效果很好:

SELECT b.*, COUNT(*) AS Editions_count
FROM Books b JOIN Books g USING (Edition_Group_ISBN)
WHERE b.ISBN IN (9780140447897, 9781934356555)
GROUP BY b.book_id;

COUNT(*) 仅支持普通通配符 *,这意味着对组中的所有行进行计数。

或者,您可以使用特定表达式,例如 COUNT(g.Edition_Group_ISBN),这意味着对组中该表达式不为空的所有行进行计数。

但是你不能使用g.*,因为它是不明确的。它会计算组中的所有行吗? (如果是这样,只需使用 COUNT(*))它是否计算组中的非空行?无论如何,这意味着什么 - 对 g 中的所有列均非空的行进行计数,或者对 g< 中的任何列进行计数/code> 非空?由于这些原因,COUNT(g.*) 根本不是 SQL 语言中的合法结构。

Try this much simpler query:

SELECT b.*, COUNT(*) AS Editions_count
FROM Books b JOIN Books g USING (Edition_Group_ISBN)
WHERE b.ISBN = 9780140447897
GROUP BY b.book_id;

I think you were making the problem much harder than you needed to. Your original query is full of confusing subqueries, at least one of which is totally superfluous.


Re your comments:

Yes, it works fine to compare ISBN to multiple values this way:

SELECT b.*, COUNT(*) AS Editions_count
FROM Books b JOIN Books g USING (Edition_Group_ISBN)
WHERE b.ISBN IN (9780140447897, 9781934356555)
GROUP BY b.book_id;

COUNT(*) supports only a plain wildcard * which means count all rows in the group.

Or you can use a specific expression like COUNT(g.Edition_Group_ISBN) which means count all rows in the group where that expression is non-null.

But you can't use g.* because it's ambiguous. Does it count all rows in the group? (if so, just use COUNT(*)) Does it count non-null rows in the group? What would that mean anyway -- count rows where all columns from g are non-null, or where any columns from g are non-null? For these reasons, COUNT(g.*) is simply not a legal construction in the SQL language.

感悟人生的甜 2024-09-18 08:28:06

该查询似乎包含许多同义反复 - 始终正确的陈述。

我认为你可以将其简化为

 SELECT books.*, b.Editions_Count 
     FROM Books 
LEFT JOIN ((SELECT Edition_Group_ISBN, COUNT(*) AS Editions_Count 
              FROM Books 
              GROUP BY Edition_Group_ISBN) AS b 
       ) ON (Books.Edition_Group_ISBN = b.Edition_Group_ISBN) 
 WHERE ISBN = 9780140447897

subselect (WHERE .. IN) 和 JOIN 条件 ID!=NULL 是多余的。

您可以通过在 Edition_Group_ISBN 上进行自连接来实现相同的目的:

SELECT b.*, count(b2.*)
FROM Books b INNER JOIN Books b2 ON (b.Edition_Group_ISBN=b2.Edition_Group_ISBN)
GROUP BY b.ISBN
HAVING b.ISBN = ...;

编辑:
修复方法是删除 JOIN 条件 Edition_Group_ISBN!=NULL,因为此表达式始终为 false。 (!= 的 NULL 操作数返回 NLLL 结果。)这使得整个连接条件为 NULL (false),因此左连接失败。

The query seems to include a number of tautologies - statements that are always true.

I think you can simplify it to

 SELECT books.*, b.Editions_Count 
     FROM Books 
LEFT JOIN ((SELECT Edition_Group_ISBN, COUNT(*) AS Editions_Count 
              FROM Books 
              GROUP BY Edition_Group_ISBN) AS b 
       ) ON (Books.Edition_Group_ISBN = b.Edition_Group_ISBN) 
 WHERE ISBN = 9780140447897

The subselect (WHERE .. IN) and the JOIN condition ID!=NULL were redundant.

You can achieve the same thing by doing a self join on the Edition_Group_ISBN:

SELECT b.*, count(b2.*)
FROM Books b INNER JOIN Books b2 ON (b.Edition_Group_ISBN=b2.Edition_Group_ISBN)
GROUP BY b.ISBN
HAVING b.ISBN = ...;

EDIT:
The fix is to remove the JOIN condition Edition_Group_ISBN!=NULL, since this expression will always be false. (A NULL operand to != returns a NLLL result.) This makes the entire join condition NULL (false) and so the left join fails.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文