时间戳

首页 > 解决方案 > 如何使用具有不同 where 条件的多项选择获得结果

sql - 如何使用具有不同 where 条件的多项选择获得结果

问题描述

我有四个表客户、订单、订单项和销售，我想获得每个客户的应付总额以及每个客户的余额。为此，我尝试了这个 SQL 查询：

SELECT c.name          AS CustomerName, 
       c.contactnumber AS CustomerContactNumber, 
       c.location      AS CustomerAddress, 
       SUM(oi.amount)  AS Amount, 
       SUM(t2.balance) AS Balance 
FROM   orderitems oi 
INNER JOIN orders o ON oi.orderid = o.id 
INNER JOIN customers c ON o.customerid = c.id 
INNER JOIN
    (SELECT s.customerid, 
            SUM(s.balance) AS Balance 
     FROM   sales s 
     INNER JOIN customers c ON s.customerid = c.id 
     GROUP BY s.customerid) t2 ON t2.customerid = c.id 
WHERE o.status == 'Processing' 
GROUP BY 
    c.id, c.contactnumber, c.location

它生成以下输出：

输出

它正确计算了应付金额，但它计算了两次余额，因为两个客户的原始余额均为 100，但每个客户显示为 200。

另请注意，订单有一个状态栏，如果订单金额到期，则显示“处理中”，如果有一些余额，则显示“未完成”。

表结构是：

订单：

订单

销售量：

销售量

订单项：

订单项

顾客：

顾客

请帮忙。

标签： sqlsqlite

解决方案

您正在对外部查询中的余额求和，但您不应该这样做，因为它是在子查询中求和的。您可以按余额分组（这不是很好的设计理论，但它很实用）；或者你可以对 sum(amount) 做一个单独的子查询，然后加入余额而不分组。

SELECT c.name          AS CustomerName, 
       c.contactnumber AS CustomerContactNumber, 
       c.location      AS CustomerAddress, 
       SUM(oi.amount)  AS Amount, 
       t2.balance      AS Balance
FROM   orderitems oi 
INNER JOIN orders o ON oi.orderid = o.id 
INNER JOIN customers c ON o.customerid = c.id 
INNER JOIN (
   SELECT customerid, SUM(balance) AS Balance
   FROM   sales
   GROUP BY customerid
) t2 ON t2.customerid = c.id 
WHERE o.status == 'Processing' 
GROUP BY c.id, c.contactnumber, c.location, c.name, t2.balance

作为2个子查询：

SELECT c.name          AS CustomerName, 
       c.contactnumber AS CustomerContactNumber, 
       c.location      AS CustomerAddress, 
       t3.Amount       AS Amount,
       t2.balance      AS Balance
FROM   customers c
INNER JOIN ( -- subquery to return Amount for each customerid
   select o.customerid, sum(oi.amount) AS Amount
   FROM orderitems oi
   INNER JOIN orders o ON oi.orderid = o.id 
   GROUP BY o.customerid
   WHERE o.status == 'Processing'
) t3 on t3.cusomterid = c.id
INNER JOIN ( -- subquery to return balance for each customerid
   SELECT customerid, SUM(balance) AS Balance
   FROM   sales
   GROUP BY customerid
) t2 ON t2.customerid = c.id

推荐阅读