MySQL SQL 左连接多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9722801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL left joining multiple tables
提问by RvdK
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 |
+-------------+---------+
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.
现在我想要一份来自“NL”的客户按汽车名称以及 2012-1-1 和 2012-2-1（按周）销售的汽车数量的列表。日期应始终存在。
For example:
例如：
+----------+----------+-------+
| 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 | ... | ... |
+----------+----------+-------+
I came up with this:
我想出了这个：
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;
What am I missing?
我错过了什么？
回答by Alex L
You are thinking in right way...
你的想法是正确的...
SELECT WEEKOFYEAR(carsales.sell_time) as 'Week', car.name, COUNT(carsales.car_sale_id)
FROM carsales
LEFT JOIN car ON car.id=carsales.car_id
LEFT JOIN customer ON customer.customer_id=carsales.customer_id
WHERE carsales.sell_time BETWEEN '2012-01-01' AND '2012-02-01'
AND customer.country = 'NL'
GROUP BY 'Week', car.name
ORDER BY 'Week', car.name;
回答by DRapp
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.
您需要首先创建结果集，显示您期望的时间段内所有可能的“周”。这是基于不完全知道你的日历表的内容......它代表每一天吗？或者只是给定一周的第一天。如果是这样，我就得稍微调整一下。然后，通过笛卡尔结果对汽车类型进行计算，以便每辆车始终在每周显示。最后，我们可以对销售数据进行 LEFT JOIN（因此我们不会丢失周数或汽车）。根据您对特定国家/地区的资格，也将其加入客户。
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.
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
回答by blthiewes
The information you are looking for is in the carsales table. I would start there and left inner join car and calendar.
您要查找的信息位于 carsales 表中。我会从那里开始，然后离开内部连接汽车和日历。
