SQL 左连接多个表 [英] SQL left joining multiple tables

问题描述

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

I'm trying to do a select of multiple tables while also having empty columns. For example my tables:

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 之间(按周)销售的汽车数量的列表.日期应始终存在.

Now I want a list of the amount of cars sold by customers from 'NL', by Car name, and between 2012-1-1 and 2012-2-1 (by week). The date should always be present.

例如:

+----------+----------+-------+
|   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;

我错过了什么?

推荐答案

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

You need to first create result set that shows all possible "weeks" within the time period you are expecting. This is based on not exactly knowing the content of your calendar table... Does it represent every day? or just the first day of a given week. If so, I'll have to adjust it a bit. Then, get that with a Cartesian result against the car types so each car always shows for each week. Finally, we can LEFT JOIN (so we don't loose weeks nor cars) to the sales data. Left join that to customers too based on your qualification of a specific country.

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

The SUM( IF()) is to ensure you ONLY count those that have a car sale, and a customer, and the customer IS within "NL" country. Any other sales will be ignored.

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

这篇关于SQL 左连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！