使用与分组数据行的联接 [英] Using a Join with Grouped Data Rows

问题描述

我有两张表，发票和 deposit 。

他们看起来有点像这样：

INVOICES

  id | paymentType |总计| dateTime 
 1 |现金| 1000 | UNIX TIME 
 2 |卡| 1350 | UNIX TIME 
 3 |卡| 1250 | UNIX TIME 
 4 |卡| 750 | UNIX TIME 
  
 
 
 
 DEPOSITS 
  id | paymentType | invNo | dateTime | amount 
 1 |现金| 1 | UNIX时间| 150 
 2 |卡| 2 | UNIX时间| 350 
  
存款总是过去的日期，发票日期将是，例如今天，因此我想确定今天在发票上支付的余额，即 invoices.grossTotal  -  deposits.amount ，并列出付款类型。
 
 
 
因此，在上面的表格示例中，发票2上的发票1和£1000都支付£850，这很容易实现一个或两个行，但在分组支付类型和存款发票时我被卡住了... 
  SELECT 
 invoices.id，
 sum（grossTotal）-IFNULL（depositsCheck.previouslyPaid，0）as todayTotal，
 depositsCheck.previouslyPaid，sum（grossTotal）as grossTotal 
 FROM`invoices` 
 LEFT JOIN（SELECT SUM（amount）as previouslyPaid，invNo 
 FROM deposits 
 GROUP BY invNo）depositsCheck ON depositsCheck.invNo = invoices.id 
 GROUP BY invoices.paymentType ORDER BY id DESC 
 
上面的SQL查询适用于使用CASH付款的项目，但不适用于卡付款，因为，分组 invoices.paymentType 意味着发票 id  c>表不再正确，因此如果此行具有与 id 无关的存储， JOIN   
 
 
如何运行上面的查询，但确保我可以加入  
 
 
 
我使用的是mySql，所以请注意， post连接MySql可以做！ ：D  
问题是当你使用 GROUP BY 时，您只能 SELECT 汇总和您分组的列。
 
 
 
  invoices.id 是您尝试选择但未分组的列。我想您可能希望将此列添加到 GROUP BY 子句中。
  SELECT 
 invoices.id，
 sum（totalTotal）-IFNULL（depositsCheck.previouslyPaid，0）as todayTotal，
 depositsCheck.previouslyPaid，sum（grossTotal）as grossTotal 
 FROM `invoices` 
 LEFT JOIN（SELECT SUM（amount）as previousPaid，invNo 
 FROM deposits 
 GROUP BY invNo）depositsCheck ON depositsCheck.invNo = invoices.id 
 GROUP BY invoices。 paymentType，invoices.id ORDER BY id DESC 
  
对于您提供的示例表， 
  id | paymentType |总计| dateTime | previousPaid 
 1 |现金| 1000 | UNIX时间| 150 
 2 |卡| 1350 | UNIX时间| 350 
 3 |卡| 1250 | UNIX时间| 0 
 4 |卡| 750 | UNIX时间| 0 
  
但一般来说，你会有类似的：
  id | paymentType |总计| dateTime | previousPaid 
 1 | Cash | 1000 | UNIX时间| 150 
 1 |卡| 1000 UNIX时间| 300 
 2 |现金| 1350 | UNIX时间| 350 
 2 |卡| 1350 | UNIX时间| 350 
  
您可以在上面看到，对于发票1，150是以现金支付，通过卡。
 
I have two tables, invoices and deposits.

They look a bit like this:

INVOICES
id    |    paymentType     |     grossTotal   |    dateTime
1     |         Cash       |        1000      |   UNIX TIME    
2     |         Card       |        1350      |   UNIX TIME   
3     |         Card       |        1250      |   UNIX TIME   
4     |         Card       |        750       |   UNIX TIME 
DEPOSITS
id    |    paymentType     |       invNo      |    dateTime    |     amount
1     |         Cash       |         1        |    UNIX TIME   |       150
2     |         Card       |         2        |    UNIX TIME   |       350
The deposits are always past dates, and the invoice dates will be, for example today, so I want to determine the balance paid today on an invoice, ie, invoices.grossTotal - deposits.amount, and list by payment Type.

So, in the table example above, there is £850 would have been paid on  Invoice 1 and £1000 on invoice 2, this is simple to achieve with one or two rows of each, but when grouping payment types and deposit invoices I am stuck...
SELECT 
   invoices.id, 
   sum(grossTotal)-IFNULL(depositsCheck.previouslyPaid,0) as todayTotal, 
   depositsCheck.previouslyPaid, sum(grossTotal) as grossTotal  
FROM `invoices`    
    LEFT JOIN (SELECT SUM(amount) as previouslyPaid, invNo 
    FROM deposits 
    GROUP BY invNo) depositsCheck ON depositsCheck.invNo=invoices.id 
GROUP BY  invoices.paymentType ORDER BY id DESC
The SQL Query above, will work for the item paid for with CASH, but not for the Card payments, because, grouping invoices.paymentType means that the id column from the invoices table is no longer correct, so the JOIN fails if this row has an id to which no deposit relates.

How can I run a query as above, but ensuring that I can join the deposits table on any instance of an invoice, grouped by payment type that matches the grouped column id records?

I am using mySql, so please post joins that MySql can do! :D
解决方案
The problem is that when you use GROUP BY, you can only SELECT aggregates and the columns you have grouped on.

invoices.id is a column you tried to select, but did not group. I think you probably want to add this column to the GROUP BY clause.
SELECT 
   invoices.id, 
   sum(grossTotal)-IFNULL(depositsCheck.previouslyPaid,0) as todayTotal, 
   depositsCheck.previouslyPaid, sum(grossTotal) as grossTotal  
FROM `invoices`    
    LEFT JOIN (SELECT SUM(amount) as previouslyPaid, invNo 
    FROM deposits 
    GROUP BY invNo) depositsCheck ON depositsCheck.invNo=invoices.id 
GROUP BY  invoices.paymentType, invoices.id ORDER BY id DESC
For the example tables you gave, it will probably give:
id    |    paymentType     |     grossTotal   |    dateTime   |   previouslyPaid
1     |         Cash       |        1000      |   UNIX TIME   |       150
2     |         Card       |        1350      |   UNIX TIME   |       350
3     |         Card       |        1250      |   UNIX TIME   |         0
4     |         Card       |        750       |   UNIX TIME   |         0
But in general, you will have something like:
id    |    paymentType     |     grossTotal   |    dateTime   |   previouslyPaid
1     |         Cash       |        1000      |   UNIX TIME   |       150
1     |         Card       |        1000      |   UNIX TIME   |       300
2     |         Cash       |        1350      |   UNIX TIME   |       350
2     |         Card       |        1350      |   UNIX TIME   |       350
Where you can see above, for invoice 1, 150 was paid in cash, and 300 was paid by card.

                        
这篇关于使用与分组数据行的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！