左外连接出现问题 [英] Trouble with LEFT OUTER JOIN

问题描述

我正在尝试获取按销售数量和日期排列的产品订单清单.我还想显示列表中未售出的产品，因此我尝试首先执行子查询，但MYSQL给出了以下消息:

I'm trying to get a list of products order by the amount sold and by date. I also want to display the products that haven't been sold in the list so I tried doing a subquery first but MYSQL is giving me this message:

操作数应包含1列

SELECT product.product_id, 
       product.product_brand_id, 
       product.product_model_id, 
       product.product_subcategory_id, 
       product.product_retail_price, 
       product.product_wholesale_price
FROM product
WHERE product.product_subcategory_id = $subcategory_id 
AND (SELECT SUM(product_sold.product_quantity) AS product_quantity_sold, 
            SUM(product_sold.product_total_price) AS total_price_sold 
     FROM product
     INNER JOIN product_sold 
        ON product.product_id = product_sold.product_id
     INNER JOIN sales 
        ON sales.sales_id = product_sold.product_sales_id 
     WHERE sales.sales_approved = '1' 
       AND sales.sales_approved_time > '$start_timestamp' 
       AND sales.sales_approved_time < '$end_timestamp')

子查询不起作用，因此我尝试使用LEFT OUTER JOIN，如另一位成员建议的那样，使用此查询:

The subquery did not work, So i tried using LEFT OUTER JOIN as suggested by another member with this query:

SELECT product.product_id, 
       product.product_brand_id, 
       product.product_model_id, 
       product.product_subcategory_id, 
       product.product_retail_price, 
       product.product_wholesale_price, 
       SUM(product_sold.product_quantity) AS product_quantity_sold, 
       SUM(product_sold.product_total_price) AS total_price_sold 
FROM product
LEFT OUTER JOIN product_sold ON product.product_id = product_sold.product_id 
                            AND product.product_subcategory_id = $subcategory_id
LEFT OUTER JOIN sales ON sales.sales_id = product_sold.product_sales_id 
WHERE sales.sales_approved = '1' 
  AND sales.sales_approved_time > '$start_timestamp' 
  AND sales.sales_approved_time < '$end_timestamp'
GROUP BY product.product_id 
ORDER BY SUM(product_sold.product_quantity) DESC

但是使用LEFT OUTER JOIN进行的查询仅给出了我出售的产品列表，我想要的是还显示列表中未出售的产品.

But this query with LEFT OUTER JOIN is giving me the list of product sold only, what I want is to also show the products that haven't been sold in the list.

以下是使用的架构sqlfiddle.com/#！2/967ee

Here is the schema used sqlfiddle.com/#!2/967ee

推荐答案

此查询将起作用，将SELECT个产品表中的更多列添加到SELECT和GROUP BY子句中

This query will work, to SELECT more columns from the products table add them to both the SELECT and GROUP BY clauses:

SELECT p.product_id
    ,SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold
    ,SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold 
FROM product p
LEFT JOIN product_sold ps ON p.product_id = ps.product_id
LEFT JOIN sales s ON ps.product_sales_id = s.sales_id
WHERE p.product_subcategory_id = $subcategory_id
  AND ( s.sales_id IS NULL
       OR ( s.sales_approved = '1' 
          AND s.sales_approved_time > '$start_timestamp'  
          AND s.sales_approved_time < '$end_timestamp'
          )
       )
GROUP BY p.product_id
ORDER BY SUM(IFNULL(ps.product_quantity,0)) DESC

说明

如果您可以在WHERE子句中使用product_sold_approved和product_sold_approved_time而不是sales表中的值，则此查询会简单得多.

This query would have been much simpler if you could have used product_sold_approved and product_sold_approved_time in the WHERE clause instead of values from the sales table.

您LEFT JOIN product_sold，LEFT JOIN表示您保留products表中的所有记录，并且已售出的记录将加入每个product_sold记录.然后，对sales表执行相同的操作.

You LEFT JOIN product_sold, a LEFT JOIN means you keep all records from the products table, and those that have been sold will get joined to each of the product_sold records. Then you do the same for the sales table.

因此，在此阶段，您有很多行为product + product_sold + sales，但您还拥有所有未售出的产品product + NULL + NULL.您需要过滤出匹配的销售字段不符合您的条件的所有联接记录，但是您需要将所有未能联接的记录留空.

So at this stage, you have lots of rows that are product + product_sold + sales but you also have all the unsold products product + NULL + NULL. You need to filter out all the joined records where the matching sale fields do not meet your criteria, but you need to leave all the records that failed to join alone.

要实现此目的，您需要一个WHERE子句，该子句分别处理每组记录. WHERE (condition A) OR (condition B).

To achieve this you have a WHERE clause that deals with each set of records separately. WHERE (condition A) OR (condition B).

条件A处理我们未售出的产品WHERE s.sales_id IS NULL-所有无法加入销售的记录都包含在结果集中，而不必匹配其他条件.

Condition A deals with our unsold products, WHERE s.sales_id IS NULL - all records that couldn't join to a sale are included in the result set and don't have to match the other criteria.

OR (sales_approved = 1 AND ... AND ...)记录s.sales_id不为NULL的记录将必须通过where子句的这一部分，因此我们可以过滤掉不必要的销售.

OR (sales_approved = 1 AND ... AND ...) records where s.sales_id isn't NULL will have to pass this half of the where clause, hence we filter out unwanted sales.

最后，我们得到一个结果集，其中包含没有任何销售的所有记录+满足您条件的所有product/product_sales/sales记录.然后，我们可以GROUP BY product_id并求和剩下的.

At the end we're left with a result set that contains all records that didn't have any sales + all product/product_sales/sales record that met your criteria. Then we can just GROUP BY product_id AND SUM what's left.

我的SQL中有IFNULL，这是因为如果您要对很多值求和，其中一些可能是NULL，如果单个值是NULL，则SUM的结果是NULL. IFNULL(column,0)通过将任何NULL值转换为0来防止这种情况. 1 + NULL = NULL，1 + 0 = 1. 但是，在反思中，我可能不需要查询-删除它们，您应该会注意到更改.

I have IFNULLs in my SQL, these are because if you're summing lots of values and some of them might be NULL, if a single value is NULL then the result of the SUM is NULL. The IFNULL(column,0) protects against this by converting any NULL values to a 0. 1 + NULL = NULL, 1 + 0 = 1. HOWEVER, on reflection I thats probably not needed for the query - remove them and you should notice the change.

这篇关于左外连接出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！