MySQL 查询零值

Question

我有这两个表(文章和销售):

id  |  name     |
====+============
1   |   milk    |
2   |   apple   |
3   |   bread   |
... |    ...    |


id  |   idArticle  |   date     |
====+==============+=============
1   |       2      | 2011-01-01 |
2   |       2      | 2011-01-01 |
3   |       3      | 2011-01-01 |
4   |       1      | 2011-01-02 |
... |      ...     |    ...     |

我需要获取 2011/01/01 的销售额:文章和计数

2011-01-01 Milk 0
2011-01-01 Apple 2
2011-01-01 Bread 1
...

但是我不知道如何显示“牛奶:0”，因为那天没有卖牛奶。

此查询不起作用:

SELECT s.date, a.name, COUNT(*)
FROM article a
LEFT JOIN sale s ON a.id = s.idArticle
WHERE s.date = "2011-01-01"
GROUP BY s.date, a.name

Answer 1

SELECT "2011-01-01" AS date, a.name, IFNULL(s.total, 0)
FROM article a
LEFT JOIN (
    SELECT idArticle, COUNT(*) AS total
    FROM sale
    WHERE date = "2011-01-01"
    GROUP BY idArticle
) AS s ON a.id = s.idArticle