带有条件的多个表上的 mySQL 左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4704095/
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
mySQL Left Join on multiple tables with conditions
提问by Jarrod
I have a problem. I have 4 tables:
我有个问题。我有4张桌子：
Invoice_Payment, Invoice, Client, and Calendar
Invoice_Payment、发票、客户和日历
Basically, I have the below query, which works well, except, months with no date_due don't return. I.E only months with a date_due will be returned.
基本上，我有以下查询，效果很好，除了没有 date_due 的月份不会返回。IE 只会返回带有 date_due 的月份。
Note: the calendar table simply lists every day of the year. It contains a single col call date_field
注意：日历表只列出一年中的每一天。它包含一个 col 调用 date_field
Database: http://oberto.co.nz/jInvoice.txt
数据库：http: //oberto.co.nz/jInvoice.txt
Expected output: my current query, below, will return something like:
预期输出：下面我当前的查询将返回如下内容：
month total
August 5
September 0
October 196
November 205
December 214
January 229
Notice how September isn't returned? It's because table Invoice_Payment has no date_due record
请注意九月没有返回？这是因为表 Invoice_Payment 没有 date_due 记录
I think I have to use a left join and join the calendar table with something like: LEFT JOIN Calendar ON Invoice_Payments.date_paid = Calendar.date_field. But I'm having no luck
我想我必须使用左联接并加入日历表，例如：LEFT JOIN Calendar ON Invoice_Payments.date_paid = Calendar.date_field。但我没有运气
SELECT MONTHNAME(Invoice_Payments.date_paid) as month, SUM(Invoice_Payments.paid_amount) AS total
FROM Invoice, Client, Invoice_Payments
WHERE Client.registered_id = 1
AND Client.id = Invoice.client_id
And Invoice.id = Invoice_Payments.invoice_id
AND date_paid IS NOT NULL
GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid)
Any help appreciated.
任何帮助表示赞赏。
回答by Thomas
It sounds like you are trying to find values for all dates within a range regardless of whether there is a value or not. Supposing we have a Calendar table structured like so:
听起来您正在尝试查找范围内所有日期的值，而不管是否有值。假设我们有一个结构如下的日历表：
Create Table Calendar
(
[Date] not null Primary Key
)
Your query might look like so (where X and Y represent the start and end date of the range in which you are investigating):
您的查询可能如下所示（其中 X 和 Y 表示您正在调查的范围的开始和结束日期）：
Select Year(C.Date), MonthName(C.Date) As Month
, Coalesce(Sum(IP.paid_amount),0) As Total
From Calendar As C
Left Join (Invoice As I
Join Client As C1
On C1.id = I.client_id
And C.registered_id = 1
Join Invoice_Payments As IP
On IP.Invoice_id = I.Id)
On IP.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)
Technically, the above query should do the trick. However, another alternative is to use a derived table about which you inquired in the comments:
从技术上讲，上面的查询应该可以解决问题。但是，另一种选择是使用您在评论中查询的派生表：
Select Year(C.Date), MonthName(C.Date) As Month
, Coalesce(Sum(Z.paid_amount),0) As Total
From Calendar As C
Left Join (
Select IP.date_paid, IP.paid_amount
From Invoice As I
Join Client As C1
On C1.id = I.client_id
And C.registered_id = 1
Join Invoice_Payments As IP
On IP.Invoice_id = I.Id
) As Z
On Z.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)
回答by todd
I've run into a similar issue and solved it by moving the non calendar table conditions out of the WHERE clause and into the join. See my full explanation here: https://gist.github.com/1137089
我遇到了类似的问题，并通过将非日历表条件从 WHERE 子句移到连接中来解决它。在此处查看我的完整解释：https: //gist.github.com/1137089
In your case you'll need to factor the from table1, table2, table3 into left join table1 on x=y etc...
在您的情况下，您需要将来自 table1、table2、table3 的因素分解为 x=y 上的左连接 table1 等...
回答by cag
I would explicitly define all your joins for readability.
为了可读性，我会明确定义您的所有连接。
A simple example of an outer join preserving all rows in the first table, filling any missing columns in the second table with nulls:
一个简单的外连接示例，保留第一个表中的所有行，用空值填充第二个表中的所有缺失列：
select column1,
from table1
left outer join table2
on table1.key = table2.key
Assuming the Calendar table is the one causing you to lose data, here's a shot at your query:
假设日历表是导致您丢失数据的表，这里是您查询的一个镜头：
SELECT MONTHNAME(Invoice_Payments.date_paid) as month,
SUM(Invoice_Payments.paid_amount)
FROM Invoice
JOIN Client
ON Client.id = Invoice.client_id
AND Client.registered_id = 1
JOIN Invoice_Payments
ON Invoice_Payments.invoice_id = Invoice.id
LEFT OUTER JOIN Calendar
ON Calendar.key = #yourothertable#.key
WHERE date_paid IS NOT NULL
GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid)
Good luck!
祝你好运！
