sql - 使用查询sql连接3个表

Question

我尝试像这样连接 3 个表:

表 1:国家

Id   |  Country
-------------------
1    |  UK
2    |  USA
3    |  France

表 2:日期

Id        |  Date
-----------------
20000101  | 2000-01-01
20000102  | 2000-01-02
...
20140901  | 2014-09-01

表 3:客户

Id    |  Customer   | Date_Creation_Id   | Country_Id
---------------------------------------------
1     |  AAA        | 20000102           | 1
2     |  B          | 20000102           | 2
2     |  CC         | 20000103           | 2

我想查找所有日期和所有国家/地区的新客户数量。

Date     | Country   | number of creation
-------------------------------------------------
20000101 | UK        | 0
20000101 | USA       | 0
20000101 | France    | 0

20000102 | UK        | 1
20000102 | USA       | 2
20000102 | France    | 0

20000103 | UK        | 0
20000103 | USA       | 1
20000103 | France    | 0

我试试这个查询

select count(*) as count,Customer.Date_Creation_Id, Customer.Country_Id
from customer 
Right join Date on Dates.Id = Customer.Date_Creation_Id
Right join Country on Country.Id = Customer.Country_Id
group by Customer.Date_Creation_Id, Customer.Country_Id

但是我没有所有的日期

Answer 1

您需要先生成所有国家和日期的列表，使用cross join，然后left join您的数据:

select d.id as date_id, c.id as country_id, count(cu.id) as cnt
from dates d cross join
     country c left join
     customers cu
     on cu.date_creation_id = d.id and cu.country_id = c.id
group by d.id, c.id
order by d.id, c.id;