我有两个表,一个跟踪产品的购买情况(用EAN表示(,另一个跟踪商品的销售情况。但并不是所有售出的产品都在购买表中,反之亦然。例如:
购买
PurchaseNo | EAN |
---|---|
1 | 0001 |
2 | 0002 |
3 | 0003 |
4 | 0004 |
您可以先分别计算采购和销售的COUNT
,然后将LEFT JOIN
作为最后的操作,以提高查询效率。
SELECT ProductEAN.EAN AS EAN,
COALESCE(Purchases.num_purchases, 0) AS num_purchases,
COALESCE(Sales.num_sales, 0) AS num_sales
FROM ProductEAN
LEFT JOIN (SELECT EAN,
COUNT(EAN) AS num_purchases
FROM Purchase
GROUP BY EAN ) Purchases
ON ProductEAN.EAN = Purchases.EAN
LEFT JOIN (SELECT EAN,
COUNT(EAN) AS num_sales
FROM Sale
GROUP BY EAN ) Sales
ON ProductEAN.EAN = Sales.EAN
因此,您可以获取此代码并将其存储在视图中。
请在此处查看演示。