mysql - 使用 MySQL JOINS 时出现问题

Question

我使用以下代码获取按日期的销售信息:

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
INNER JOIN product_sold 
  ON product.product_id = product_sold.product_id AND product.product_subcategory_id = '$subcategory_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'
GROUP BY product.product_id 
ORDER BY SUM(product_sold.product_quantity) DESC

上面的代码正确地提取了信息，但是当我尝试获取已售产品和未售出产品的信息时，我运行以下代码:

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, product_brand.brand_name, product_model.model_name, product_subcategory.subcategory_name
FROM product
LEFT JOIN product_sold 
  ON product.product_id = product_sold.product_id AND product.product_subcategory_id = '$subcategory_id'
LEFT JOIN sales 
  ON sales.sales_id = product_sold.product_sales_id AND sales.sales_approved = '1' AND sales.sales_approved_time > '$start_timestamp' AND sales.sales_approved_time < '$end_timestamp'
INNER JOIN product_brand 
  ON product_brand.brand_id = product.product_brand_id
INNER JOIN product_model 
  ON product_model.model_id = product.product_model_id
INNER JOIN product_subcategory 
  ON product_subcategory.subcategory_id = product.product_subcategory_id
WHERE product.product_subcategory_id = '$subcategory_id'
GROUP BY product.product_id 
ORDER BY SUM(product_sold.product_quantity) DESC, product_brand.brand_name ASC, product_model.model_name ASC

当我运行该代码时，该代码可以运行，但不能按日期运行，它会从数据库中获取所有数据并显示信息。

我需要的是显示已售出的产品，然后添加一种子查询来显示尚未售出的产品。

<小时/>

我找到的最终解决方案就是这个。

SELECT p.product_id, p.product_brand_id, p.product_model_id, p.product_subcategory_id, p.product_retail_price, p.product_wholesale_price, SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold,SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold, pb.brand_name, pm.model_name, psub.subcategory_name
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
JOIN product_brand pb ON pb.brand_id = p.product_brand_id
JOIN product_model pm ON pm.model_id = p.product_model_id
JOIN product_subcategory psub ON psub.subcategory_id = p.product_subcategory_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 product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC

Answer 1

这可能更接近您想要的。我将销售表移到了前面，因为这是您查询的基础...日期和销售批准状态...从销售中，转到由销售 ID 连接的已售产品...然后转到产品表及其相关的子表(根据需要)。可能需要一些小的调整，但应该会让你走上正确的道路。

SELECT 
      p.product_id, 
      p.product_brand_id, 
      p.product_model_id, 
      p.product_subcategory_id, 
      p.product_retail_price, 
      p.product_wholesale_price, 
      SUM(ps.product_quantity) AS product_quantity_sold, 
      SUM(ps.product_total_price) AS total_price_sold, 
      pb.brand_name, 
      pm.model_name, 
      psub.subcategory_name
   FROM 
      sales s 
         JOIN product_sold ps
            s.sales_id = ps.product_sales_id
            JOIN product p
               ON ps.product_id = p.product_id 
               AND p.product_subcategory_id = '$subcategory_id'
               INNER JOIN product_brand pb
                  ON p.product_brand_id = pb.brand_id
               INNER JOIN product_model pm
                  ON p.product_model_id = pm.model_id 
               INNER JOIN product_subcategory psub 
                  ON p.product_subcategory_id = psub.subcategory_id
   WHERE 
          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(ps.product_quantity) DESC, 
      pb.brand_name, 
      pm.model_name

这是对所有产品执行的另一个查询，如果有任何合格销售，也会得到该结果...与上面不同的是，首先特定于所有合格销售。请注意，每个其自己的 where 子句和 sum/group by 都有一个针对 sales、product sales 和 products 的子查询，因此它已按产品 ID 分组为单个记录。这将加入到您的整体产品表中...

SELECT 
      p.product_id, 
      p.product_brand_id, 
      p.product_model_id, 
      p.product_subcategory_id, 
      p.product_retail_price, 
      p.product_wholesale_price, 
      SUM(SummedSales.product_quantity_sold) AS product_quantity_sold, 
      SUM(SummedSales.total_price_sold) AS total_price_sold, 
      pb.brand_name, 
      pm.model_name, 
      psub.subcategory_name
   FROM 
      product p
         INNER JOIN product_brand pb
            ON p.product_brand_id = pb.brand_id
         INNER JOIN product_model pm
            ON p.product_model_id = pm.model_id 
         INNER JOIN product_subcategory psub 
            ON p.product_subcategory_id = psub.subcategory_id
         LEFT JOIN ( select
                           ps.product_id,
                           SUM(ps.product_quantity) AS product_quantity_sold, 
                           SUM(ps.product_total_price) AS total_price_sold
                        from
                           sales s
                              JOIN product_sold ps
                                 ON s.sales_id = ps.product_sales_id
                                 JOIN product p
                                    ON ps.product_id = p.product_id 
                                    AND p.product_subcategory_id = '$subcategory_id'
                        WHERE 
                               s.sales_approved = '1' 
                           AND s.sales_approved_time > '$start_timestamp' 
                           AND s.sales_approved_time < '$end_timestamp' ) SummedSales
            ON P.productID = SummedSales.product_id
   GROUP BY 
      p.product_id 
   ORDER BY 
      SUM(SummedSales.product_quantity_sold) DESC, 
      pb.brand_name, 
      pm.model_name