即使不是一个表中的所有行在另一个表中都有corespondents，如何从MySQL的两个表中进行选择呢? [英] How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

问题描述

我有两张网上商店的桌子:

I have two tables for an online shop:

• 一个类别:id，标题
• 一个产品:ID，OwnerID，标题，价格(ownerid是父类别的ID)

我要选择所有类别，还要选择每个类别的最低和最高价格，因此需要以下查询:

I want to select all the categories and also select the minimum and maximum price in each, hence the following query:

SELECT
sc.*, MIN(s.price) AS minp, MAX(s.price) AS maxp
FROM
categories AS sc, products AS s
WHERE s.ownerid=sc.id
GROUP BY sc.id

它的工作原理与预期相符，唯一的例外是，如果类别中没有任何产品，则不会选择该类别.尽管自从我要求输入"s.ownerid = sc.id"以来这似乎合乎逻辑，但我不知道足够的SQL使其能够按预期工作.我需要所有类别，对于没有产品的类别，minp和maxp应该为0.

It works pretty much as expected with the only exception that if a category doesn't have any product in it, then it is not selected. While this seems logical since I ask for "s.ownerid=sc.id", I don't know enough SQL to make it work as intended. I need all the categories and for the ones that don't have products minp and maxp should be 0.

有什么建议吗?谢谢.

推荐答案

为此，您需要一个外部联接.顺便说一句，您使用隐式联接编写查询的方式已经过时，不再建议使用.建议使用JOIN关键字.这也使将内部联接更改为外部联接变得更加容易.

To do this you need an outer join. By the way, the way you are writing your query with an implicit join is outdated and no longer recommended. Using the JOIN keyword is recommended. This also makes it easier to change an inner join to an outer join.

FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid=sc.id

要返回0而不是NULL，请使用 IFNULL(..., 0) .整个查询变为:

To return 0 instead of NULL use IFNULL(..., 0). The entire query becomes:

SELECT
    sc.*,
    IFNULL(MIN(s.price), 0) AS minp,
    IFNULL(MAX(s.price), 0) AS maxp
FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid = sc.id
GROUP BY sc.id

对于没有产品的类别，您可能还想考虑最好返回默认的NULL而不是0.

You may also want to consider if it would be better to return the default NULL instead of 0 for categories that have no products.

这篇关于即使不是一个表中的所有行在另一个表中都有corespondents，如何从MySQL的两个表中进行选择呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！