sqlite - 如何正确连接这两个表？

Question

我有这两张 table

顾客:

id | name                | address
---+---------------------+-------------------------
1  | company 1           | some address information
2  | company 2           | another address
3  | yet another company | no address here

订单:

id | customer_id | date
---+-------------+---------
1  | 2           | 20151209
2  | 2           | 20151211
3  | 3           | 20151210
4  | 1           | 20151223

现在我想得到一个带有 的结果表每位客户在左侧和 内的订单数量任意时间段在右侧。
例如，假设此期间为 20151207 <= date <= 20151211，则结果表应如下所示:

name                | orders count
--------------------+-------------
company 1           | 0
company 2           | 2
yet another company | 1

注:日期 = 20151207 表示 2015 年 12 月 7 日。

如何加入他们？

Answer 1

SELECT c.name, COUNT(CASE WHEN ((o.date BETWEEN 20151207 AND 20151211) OR (o.date ISNULL)) THEN o.customer_id END) AS “Total Sales” FROM customers AS c LEFT JOIN orders o ON c.id == o.customer_id GROUP BY c.name