mySQL在有条件的多个表上左联接 [英] mySQL Left Join on multiple tables with conditions

问题描述

我有问题.我有4张桌子:

I have a problem. I have 4 tables:

发票付款，发票，客户和日历

Invoice_Payment, Invoice, Client, and Calendar

基本上，我有以下查询，它运行良好，但没有date_due的月份不会返回.即仅返回带有date_due的月份.

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.

注意:日历表仅列出一年中的每一天.它包含一个单独的col调用date_field

Note: the calendar table simply lists every day of the year. It contains a single col call date_field

数据库: 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

请注意，九月怎么不回来?这是因为表Invoice_Payment没有date_due记录

Notice how September isn't returned? It's because table Invoice_Payment has no date_due record

我认为我必须使用左联接，并使用类似以下内容联接日历表: 左加入日历Invoice_Payments.date_paid = Calendar.date_field.但是我没有运气

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

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)

任何帮助表示赞赏.

推荐答案

听起来，无论是否有值，您都在尝试查找范围内所有日期的值.假设我们有一个日历表，其结构如下:

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
)

您的查询可能看起来像这样(其中X和Y代表您要调查的范围的开始日期和结束日期):

Your query might look like so (where X and Y represent the start and end date of the range in which you are investigating):

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)

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