保留行，即使它们在其他连接表 MySQL Query 中没有任何行 [英] Keep rows, even if they dont have any rows in other join table MySQL Query

问题描述

    SELECT `products`.*, SUM(orders.total_count) AS revenue, SUM(orders.quantity) AS qty FROM `products` 
    LEFT JOIN `orders` ON (`products`.`id` = `orders`.`product_id`) 
    WHERE (`orders`.`status` = 'delivered' OR `orders`.`status` = 'new') 
    GROUP BY `products`.`ID` ORDER BY products.ID DESC LIMIT 10 OFFSET 0

这是我得到的.目前它只抓取有任何已交付或新订单的产品.

This is what I got. Currently it only grab the products which have any orders that are either delivered or new.

那个 WHERE 语句只是为了正确计算 SUM(orders.total_count) 和 SUM(orders.quantity) -

That WHERE statement is only for the correct calculation of SUM(orders.total_count) and SUM(orders.quantity) -

我想抓取所有产品，如果有此产品的任何订单，则应查看 where 语句以确保我们抓取的订单是新的或已交付的.

I would like to grab all the products, and if there is any orders for this product then it should look at that where statement to make sure that the order we grab are new or delivered.

推荐答案

简短的回答是将 WHERE 更改为 AND.

The short answer is change WHERE to AND.

实际上，这意味着orders.status"列上的谓词需要在 ON 子句中，而不是 WHERE 子句.

Really, what that means is, the predicate on the "orders.status" column needs to be in the ON clause, rather than the WHERE clause.

在您的查询中，该谓词相当于orders.status IS NOT NULL"，它将抛出"由 LEFT 创建的任何 NULL 行加入.

In your query, that predicate is doing the equivalent of an "orders.status IS NOT NULL", which is going to "throw out" any of the NULL rows that were created by the LEFT JOIN.

想到 LEFT (OUTER) JOIN 的最简单方法是，当没有其他订单"行匹配时，它会创建一个虚拟的订单"行以匹配产品"行.并且该虚拟行包含所有 NULL 值.

The easiest way to think of the LEFT (OUTER) JOIN is that it's creating a dummy "orders" row to match to a "products" row, when there is no other "orders" row that matches. And that dummy row consists of all NULL values.

这篇关于保留行，即使它们在其他连接表 MySQL Query 中没有任何行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！