mysql - HAVING 子句影响结果

Question

我有一个显示已售出和未售出产品的查询。

下面是正确工作的查询。

 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_brand_id = $brand_id AND p.product_model_id = $model_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'
      )
   )
   AND pb.brand_name NOT LIKE 'X%'
 GROUP BY p.product_id 
 ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.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, 
    SUM(IFNULL(pq.product_quantity,0)) AS total_stock, 
    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
 LEFT JOIN product_stock pq ON p.product_id = pq.product_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_brand_id = $brand_id AND p.product_model_id = $model_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'
      )
   )
   AND pb.brand_name NOT LIKE 'X%'
 GROUP BY p.product_id 
 HAVING total_stock > 0 OR product_quantity_sold > 0
 ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC

这有点奇怪，因为对于下面的变量，我在每个产品上得到不同的结果

product_quantity_sold

结果中的其他所有内容都保持不变。

Answer 1

这与HAVING无关。

您的第二个查询与 product_stock 表联接，而第一个查询没有。因此，每个相关股票条目都会重复每个预聚合记录。

例如，

product_id = 577 在 product_stock 中出现了 8 次，因此在第二次查询中其 product_quantity_sold 的值是 8 倍(184) 比第一个 (23)。