即使不是一个表中的所有行在另一个表中都有对应者，如何从 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、title
• 产品的一个:id、ownerid、title、price(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.

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