COALESCE SUM GROUP？

如果将日期条件放在JOIN子句中，则无需任何子查询即可完成此操作。

以下是我在MySQL 5.0上测试过的代码。

SELECT m.name, COALESCE(SUM(oi.quantity), 0) AS count FROM menu_items AS m LEFT OUTER JOIN ( order_items AS oi JOIN orders AS o ON (o.id = oi.order_id) ) ON (m.id = oi.menu_item_id AND o.date BETWEEN '2008-11-01' AND '2008-11-30') GROUP BY m.id; 输出：

+--------+-------+ | name | count | +--------+-------+ | bread | 2 | | milk | 1 | | honey | 2 | | cheese | 0 | +--------+-------+ 这是MySQL风格的DDL和设置代码：

DROP TABLE IF EXISTS menu_items; CREATE TABLE menu_items ( id INT PRIMARY KEY, name VARCHAR(10) ) TYPE=InnoDB;

DROP TABLE IF EXISTS orders; CREATE TABLE orders ( id INT PRIMARY KEY, date DATE ) TYPE=InnoDB;

DROP TABLE IF EXISTS order_items; CREATE TABLE order_items ( order_id INT, menu_item_id INT, quantity INT, PRIMARY KEY (order_id, menu_item_id), FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ) TYPE=InnoDB;

INSERT INTO menu_items VALUES (1, 'bread'), (2, 'milk'), (3, 'honey'), (4, 'cheese');

INSERT INTO orders VALUES (1, '2008-11-02'), (2, '2008-11-03'), (3, '2008-10-29');

INSERT INTO order_items VALUES (1, 1, 1), (1, 3, 1), (2, 1, 1), (2, 2, 1), (2, 3, 1), (3, 4, 10);