兰迪的回答很接近,但是WHERE语句删除了对这些项目的任何提及,而不是该日期范围内任何订单的一部分。
请注意,“左联接”与在WHERE子句中按您所做的方式(即内部联接)链接表不同。我建议您阅读不同类型的SQL连接(内部、外部、交叉)。
在Essense中,您需要将从Randy的查询中获得的数据加入到您的项目源列表中。使用嵌套select可以做到这一点:
SELECT
name
, nvl(count, 0) as count
FROM
menu_items items
LEFT JOIN (
SELECT
menu_items.id
, SUM(order_items.quantity) as count
FROM
menu_items
LEFT JOIN order_items ON menu_items.id = order_items.menu_item_id
LEFT JOIN orders ON orders.id = order_items.order_id
WHERE
"date" between to_date('2008-11-01','YYYY-MM-DD') and to_date('2008-11-30','YYYY-MM-DD')
GROUP BY
menu_items.id
) counts on items.id = counts.id;
这是在Oracle10gbtw中。我怀疑您使用的是Oracle,所以您需要转换到您自己的数据库。
运行测试显示以下内容:
SQL> create table menu_items ( id number, name varchar2(10));
create table order_items (order_id number, menu_item_id number, quantity number);
create table orders (id number, "date" date);
Table created.
SQL>
Table created.
SQL>
Table created.
SQL>
insert into menu_items values (1, 'bread');
insert into menu_items values (2, 'milk');
insert into menu_items values (3, 'honey');
insert into menu_items values (4, 'cheese');
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
insert into orders values (1, to_date('2008-11-02', 'YYYY-MM-DD'));
insert into orders values (2, to_date('2008-11-03', 'YYYY-MM-DD'));
insert into orders values (3, to_date('2008-10-29', 'YYYY-MM-DD'));SQL>
1 row created.
SQL>
1 row created.
SQL>
insert into order_items values (1, 1, 1);
insert into order_items values (1, 3, 1);
1 row created.
SQL>
1 row created.
SQL>
insert into order_items values (2, 1, 1);
insert into order_items values (2, 2, 1);
insert into order_items values (2, 3, 1);
insert into order_items values (3, 4, 10);
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> SQL>
1 row created.
SQL>
SELECT
name
, nvl(count, 0) as count
FROM
menu_items items
LEFT JOIN (
SELECT
menu_items.id
, SUM(order_items.quantity) as count
FROM
menu_items
LEFT JOIN order_items ON menu_items.id = order_items.menu_item_id
LEFT JOIN orders ON orders.id = order_items.order_id
WHERE
"date" between to_date('2008-11-01','YYYY-MM-DD') and to_date('2008-11-30','YYYY-MM-DD')
GROUP BY
menu_iteSQL> 2 3 4 5 6 7 ms.id
) counts on items.id = counts.id; 8 9 10 11 12 13 14 15 16 17 18
NAME COUNT
---------- ----------
bread 2
milk 1
honey 2
cheese 0
SQL>
drop table menu_items;
drop table order_items;
drop table orders;SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
PS:使用'date'作为列名是一种坏做法,因为它(在大多数情况下)是一个类型名,并且可能导致查询和解析问题。