mysql - 是否可以在 SQL 中为以下查询添加带有 SUM 和 GROUP BY 的 JOIN 查询？

Question

我可以在一个名为“incomes”的表中使用以下查询读取“cust_id”及其应付款，但我在另一个名为 customer 的表中有名称和地址 - 我需要在查询中添加 - 是否可以添加 JOIN 查询下列的？如何？

SELECT cust_id, SUM(inc_text)- (SUM(inc_amount)-  
       SUM(
           case
               when inctype_id =11 then 0
               else inc_amount
           end
       )) Total_due_left  FROM incomes 
                                
  GROUP BY cust_id;

标签： mysqlsqldatabase

Answer 1

这应该有效：

SELECT c.*,
       ( SUM(i.inc_text) - (SUM(i.inc_amount) - SUM(CASE WHEN i.inctype_id = 11 then 0 ELSE inc_amount END))
       ) as Total_due_left
FROM incomes i JOIN
     customers c
     ON c.cust_id = i.cust_id                             
GROUP BY c.cust_id;

假设它customers.cust_id是unique. 否则，您需要在andcustomers中明确列出列。SELECTGROUP BY

看起来这也可以简化为：

SELECT c.*,
       ( SUM(i.inc_text) - SUM(CASE WHEN i.inctype_id = 11 THEN inc_amount ELSE 0 END)
       ) as Total_due_left
FROM incomes i JOIN
     customers c
     ON c.cust_id = i.cust_id                             
GROUP BY c.cust_id;