MySQL:返回带有NULL或0 Vales的计数(*)

Question

我有三个表CustomerSales&冰淇淋。为了便于理解，每次销售都有一个冰淇淋。

CustomerSales表包含(CustomerId、PurchaseId、邮政编码、IcecreamID(

冰流包含(冰流名称，冰流Id(

我正试图写一个查询，将巧克力冰淇淋的销售额返回到每个邮政编码(邮政编码(，但我也想知道哪些邮政编码的销售额为零。目前，它只返回有销售额的邮政编码。

SELECT C.postcode, COUNT(*) AS TOTAL SALES
FROM CustomerSales C JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode;

这与我想要的很接近，但只包括销售的邮政编码。我还想包括2019年巧克力冰淇淋销售额为0的邮政编码。

我该怎么做？我试过使用rollup，但认为我做得不对。

我也在想

SELECT C.postcode, COUNT(*) AS TOTAL SALES
FROM CustomerSales C OUTER LEFT JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode;

我认为问题是，计数返回行的值。

Answer 1

计算PurchaseId，而不是*，我认为这应该返回预期的结果：

SELECT C.postcode, COUNT(C.PurchaseId) AS TOTAL SALES
FROM CustomerSales C OUTER LEFT JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode;

Answer 2

SELECT C.postcode, COUNT(*) AS TOTAL_SALES
FROM CustomerSales C JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode
union
select c.postcode, '0' as TOTAL_SALES
FROM CUSTOMERSALES C 
where c.postcode not in(select cs.postcode from 
FROM CustomerSales C JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
);

Answer 3

我看到的问题是，您正在进行联接，然后计算返回的记录数，因此默认情况下，没有销售额的记录不会以联接结束。

联接类型在这里很重要，我认为LEFT Join将显示第一个表中的所有数据，即使在联接表中没有找到数据，然后您将计算联接表中的字段，如果没有找到，则返回NULL

类似这样的东西：

SELECT C.postcode, COUNT(I.name) AS TOTAL SALES
FROM CustomerSales C LEFT JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode;

Answer 4

如果要ALL邮政编码，则必须将DISTINCT邮政编码的LEFT联接到CustomerSales，然后再联接到IceCream:

SELECT p.postcode, COUNT(C.PurchaseId) AS TOTAL_SALES
FROM (
SELECT DISTINCT postcode
FROM CustomerSales
) p LEFT JOIN CustomerSales C 
ON C.postcode = p.postcode AND C.saleyear = '2019'
LEFT JOIN IceCream I ON C.icecreamID = I.id AND I.name = 'Chocolate'
GROUP BY p.postcode;

Answer 5

为了清楚起见，您希望使用LEFT JOIN，将I上的条件移动到ON子句，并更改COUNT():

SELECT C.postcode, COUNT(i.id) AS TOTAL SALES
FROM CustomerSales C LEFT JOIN
IceCream I
ON C.icecreamID = I.id AND
I.name = 'Chocolate'
WHERE C.saleyear = 2019
GROUP BY C.postcode;