mysql - 即使分组依据为 0 行也显示项目

Question

我正在尝试获取下面的查询，以针对每家商店的每件商品显示我们拥有的 4 件商品中每件商品的数量。效果很好，我创建了临时表以尝试提高速度，但我的问题是，如果表中没有某个产品的行，则该产品根本不会显示。

我想显示所有四种产品 (prodNo)，而不管该特定商店是否真的有任何行。

我研究了这个网站，找不到足够相似的东西让我弄明白。

CREATE TEMPORARY TABLE IF NOT EXISTS temp_invoice_dates AS
(
    SELECT Invoice_detail.del_date,invoice_Detail.StoreNo,mast_stores.SDesc, invoice_Detail.ProdNo,sold_qty,retn_price,retn_qty,sold_price FROM Invoice_detail 
        LEFT JOIN mast_stores on invoice_detail.StoreNO=mast_stores.Snum 
        LEFT  JOIN invoice on invoice_detail.Del_Date=invoice.Del_Date and invoice_detail.Invoice_No=invoice.Invoice_No  
        WHERE Cnum IN ('200','210') AND invoice_detail.Del_Date >= "2016-03-01" AND invoice_detail.Del_Date < "2016-04-01"
);

SELECT 
    temp_invoice_dates.StoreNo,
    temp_invoice_dates.SDesc,
    DATE_FORMAT(temp_invoice_dates.Del_Date,'%Y') as Year,
    DATE_FORMAT(temp_invoice_dates.Del_Date,'%M') as Month,
    temp_invoice_dates.ProdNo,
    mast_items.IDesc,
    SUM(sold_qty) as TotalIn,
    SUM(retn_qty) as TotalOut,
    ROUND(SUM((sold_qty*sold_price)-(retn_qty*retn_price)),2) as NetSales,
    CONCAT(ROUND(SUM(retn_qty)/SUM(sold_qty),2)*100,'%') as StalePerc
    FROM mast_Items 
    LEFT JOIN temp_invoice_dates on temp_invoice_dates.ProdNo=mast_items.Inum 
    WHERE mast_items.Inum in ('3502','3512','4162','4182')
    GROUP BY temp_invoice_dates.StoreNo, ProdNo
    ORDER BY temp_invoice_dates.StoreNo, ProdNo;

Drop table temp_invoice_dates;

结果类似于:

StoreNo  Product  Count....  
1        1        1
1        2        5
1        3        2
1        4        1
2        1        14
2        2        1
2        4        4
3        2        33
3        3        3

如我所愿

StoreNo  Product  Count ....  
1        1        1
1        2        5
1        3        2
1        4        1
2        1        14
2        2        1
2        3        0
2        4        4
3        1        0
3        2        33
3        3        3
3        4        0

Answer 1

这样的事情应该可行。

SELECT sp.StoreNo, sp.ProdNo
    , ...stuff...
    , sp.IDesc, sp.SDesc
    , ...more stuff...
FROM (
   SELECT i.Inum AS ProdNo, s.Snum AS StoreNo
        , i.IDesc, s.SDesc
   FROM mast_Items AS i, mast_stores AS s
   WHERE i.Inum IN ('3502','3512','4162','4182')
   ) AS sp
LEFT JOIN temp_invoice_dates AS tid 
   ON sp.ProdNo = tid.ProdNo 
   AND sp.StoreNo = tid.StoreNo
GROUP BY sp.StoreNo, sp.ProdNo
ORDER BY sp.StoreNo, sp.ProdNo
;

通常我建议不要使用交叉连接(如在子查询中看到的那样)，但在这种情况下它正是需要的。如果查询速度很慢，您可以预先将子查询结果插入到临时表中，对其进行索引，然后使用临时表代替子查询。

(编辑:在可用于分组和结果时应使用 sp 字段)