MySQL - 查询中的复杂 SUM [英] MySQL - Complicated SUMs inside Query

问题描述

这将很难解释.

我每天多次从 tbl_customers 循环浏览我的客户记录.

I'm looping through my client records from tbl_customers several times a day.

SELECT c.* FROM tbl_customers c

我只返回客户的:customerid、name、phone、email

I'm returning simply the customer's: customerid, name, phone, email

现在是奇怪的部分.我想在电子邮件后附加 3 列:totalpaid、totalowed、totalbalance但是，这些列名在任何地方都不存在.

Now the weird part. I want to append 3 more columns, after email: totalpaid, totalowed, totalbalance BUT, Those column names don't exist anywhere.

以下是我查询每个查询的方式:(作为单个查询)

Here is how I query each one: (as a single query)

SELECT SUM(total) AS totalpaid 
FROM tbl_customers_bills 
WHERE customerid = X
AND billtype = 1

SELECT SUM(total) AS totalowed 
FROM tbl_customers_bills 
WHERE customerid = X
AND billtype = 2

SELECT SUM(total) AS totalbalance 
FROM tbl_customers_bills 
WHERE customerid = X
AND billtype IN(1,2)

因此，billtype 是告诉我记录是否已付款的列.

So, the billtype is the column that tells me whether the record is paid or not.

我在这里不知所措.如何将 3 个单独的查询汇总到第一个查询的循环中?

I am at a loss here. How can I SUM 3 separate queries into the first query's loop?

推荐答案

只需加入客户的帐单并计算总和.要将 totalpaid 和 totalowed 分开，您可以使用 SUM(CASE 或 SUM(IF) 作为 wless1 的回答证明了

Just join customers to bills and do the sums. To separate out totalpaid and totalowed you can use SUM(CASE or SUM(IF as wless1's answer demonstrates

SELECT c.*,
        SUM(CASE WHEN billtype = 1 THEN total ELSE 0 END) totalpaid ,
        SUM(CASE WHEN billtype = 2 THEN total ELSE 0 END) totalowed ,
        SUM(total) AS totalbalance
FROM 
    tbl_customers c
    LEFT JOIN tbl_customers_bills  b
    ON c.customerid = b.customerid
     and billtype in (1,2)
GROUP BY 
     c.customerid

因为这是 MySQL，所以只需要根据客户的 PK 分组即可.

Because this is MySQL you only need to group on the PK of customer.

这篇关于MySQL - 查询中的复杂 SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！