mysql - SQL左连接多个表

Question

我正在尝试选择多个表，同时也有空列。例如我的表:

calendar:  //filled with dates from 2011-01-01 to 2015-12-31
+-----------+
| datefield |
+-----------+
| 2012-1-1  |
| 2012-1-2  |
| ...       |
| 2012-2-3  |
| 2012-2-4  |
+-----------+

car: 
+--------+---------+
| car_id |  name   |
+--------+---------+
|      1 | Ford    |
|      2 | Peugeot |
|      3 | Fiat    |
+--------+---------+

carsales: 
+-------------+-----------+--------+-------------+
| car_sale_id | sell_time | car_id | customer_id |
+-------------+-----------+--------+-------------+
|           1 | 2012-1-2  |      1 |           1 |
|           1 | 2012-1-2  |      2 |           1 |
|           2 | 2012-1-3  |      3 |           2 |
+-------------+-----------+--------+-------------+

customer: 
+-------------+---------+
| customer_id | country |
+-------------+---------+
|           1 | NL      |
|           2 | EN      |
+-------------+---------+

现在我想要一份“NL”客户销售的汽车数量列表，按汽车名称，以及在 2012-1-1 和 2012-2-1 之间(按周)。日期应始终存在。

例如:

+----------+----------+-------+
|   Week   | Car name | Sales |
+----------+----------+-------+
| 1        | Ford     |     0 |
| 1        | Peugeot  |     0 |
| 1        | Fiat     |     0 |
| 2        | Ford     |     1 |
| 2        | Peugeot  |     1 |
| 2        | Fiat     |     0 |
| 3        | Ford     |     0 |
| 3        | Peugeot  |     0 |
| 3        | Fiat     |     0 |
| etc      | ...      |   ... |
+----------+----------+-------+

我想到了这个:

SELECT WEEKOFYEAR(calendar.datefield) as 'Week', car.name, COUNT(carsales.car_id)
FROM car, customer, calendar
LEFT JOIN carsales ON DATE(calendar.datefield) = DATE(carsales.sell_time)
WHERE calendar.datefield > '2012-01-01' AND calendar.datefield < '2012-02-01'
AND car.id = carsales.car_id
AND customer.country = 'NL'
AND customer.customer_id = carsales.customer_id
GROUP BY 'Week', car.name
ORDER BY 'Week', car.name;

我错过了什么？

Answer 1

您需要首先创建结果集，以显示您期望的时间段内所有可能的“周”。这是基于不完全了解您的日历表的内容......它代表每一天吗？或者只是给定一周的第一天。如果是这样，我将不得不稍微调整一下。然后，根据汽车类型获得笛卡尔结果，这样每辆车总是每周显示一次。最后，我们可以对销售数据进行 LEFT JOIN(这样我们就不会丢失周数或汽车数)。根据您在特定国家/地区的资格，也将其加入客户。

SUM( IF()) 是为了确保您只计算那些有汽车销售和客户的客户，并且客户在“NL”国家/地区内。任何其他销售都将被忽略。

SELECT 
      AllWeeksAllCars.WeekNumber,
      AllWeeksAllCars.Name,
      SUM( IF( Customer.Customer_ID > 0, 1, 0 ) ) as CarSales
   FROM 
      ( select AllWeeks.*,
               Car.car_id,
               Car.Name
           from 
              ( select 
                      WEEKOFYEAR( Calendar.DateField ) as WeekNumber,
                      MIN( Calendar.DateField ) as FirstDate,
                      MAX( Calendar.DateField ) as LastDate
                   from
                      Calendar
                   where
                          Calendar.dateField > '2012-01-01'
                      AND Calendar.dateField < '2012-01-01' 
                   group by
                      `WeekNumber` ) AllWeeks,
              car
           order by
              AllWeeks.WeekNumber,
              Car.Name  ) AllWeeksAllCars

         LEFT JOIN CarSales
            on CarSales.Sell_Time between AllWeeksAllCars.FirstDate and  AllWeeksAllCars.LastDate
           AND CarSales.Car_ID =  AllWeeksAllCars.Car_ID

             LEFT JOIN Customer
                on CarSales.Customer_ID = Customer.Customer_ID
                AND Customer.Country = 'NL'
   GROUP BY
      AllWeeksAllCars.WeekNumber,
      AllWeeksAllCars.Name