SQL查询，每月获取Net Salse

Question

我正在寻找一个查询来获得每月净销售额，我试过了，但没能得到我想要的。这是我的Order表

+----------+-----------+--------+------------+---------------+-------------+-----------+---------+---------+
| orderID  | custID    | userID | orderDate  | paymentMethod | grossAmount | netAmount | cash    | balance |
+----------+-----------+--------+------------+---------------+-------------+-----------+---------+---------+
| INV-0001 | CUST-0001 | U-001  | 2020-05-01 | Cash Pay      |      525.00 |    525.00 |  550.00 |   25.00 |
| INV-0002 | CUST-0001 | U-001  | 2020-05-01 | Cash Pay      |      240.00 |    240.00 |  250.00 |   10.00 |
| INV-0003 | CUST-0001 | U-001  | 2020-05-01 | Cash Pay      |      220.00 |    220.00 |  250.00 |   30.00 |
| INV-0004 | CUST-0001 | U-001  | 2020-04-30 | Cash Pay      |      895.00 |    895.00 | 1000.00 |  105.00 |
| INV-0005 | CUST-0001 | U-001  | 2020-04-30 | Cash Pay      |      300.00 |    300.00 |  500.00 |  200.00 |
| INV-0006 | CUST-0001 | U-001  | 2020-04-30 | Cash Pay      |      230.00 |    230.00 |  250.00 |   20.00 |
+----------+-----------+--------+------------+---------------+-------------+-----------+---------+---------+

这是我的CustomerReturn表

+-------+----------+------------+--------+------------+-----------+-----------+-------------+
| retID | orderID  | itemCode   | userID | retDate    | returnQty | unitPrice | totalAmount |
+-------+----------+------------+--------+------------+-----------+-----------+-------------+
|     1 | INV-0001 | 1800232050 | U-001  | 2020-05-01 |      1.00 |    100.00 |      100.00 |
|     2 | INV-0002 | 1909873674 | U-001  | 2020-05-01 |      2.00 |     55.00 |      110.00 |
|     3 | INV-0004 | 1800232050 | U-001  | 2020-04-30 |      1.00 |    100.00 |      100.00 |
+-------+----------+------------+--------+------------+-----------+-----------+-------------+

公式为(月账单总额(Order.netAmount(-月收益总额(CustomerReturn.totalAmount((

需要获得每年每个月的净销售额。

select orderDate,sum(netAmount)-sum(totalAmount) from `Order` o,CustomerReturn r where o.orderID=r.orderID GROUP BY orderDate;

当我运行这个查询时，它会显示这个

+------------+---------------------------------+
| orderDate  | sum(netAmount)-sum(totalAmount) |
+------------+---------------------------------+
| 2020-04-30 |                          795.00 |
| 2020-05-01 |                          555.00 |
+------------+---------------------------------+

但它应该像这个

+------------+---------------------------------+
| orderDate  | sum(netAmount)-sum(totalAmount) |
+------------+---------------------------------+
| 2020-04-30 |                         1425.00 |
| 2020-05-01 |                          775.00 |
+------------+---------------------------------+

请帮帮我。谢谢。！

Answer 1

您的查询很好，当CustomerReturn表中的OrderId匹配时，它会提取所有记录，并根据您的请求进行求和，但订单INV-0003没有返回，因此当涉及到该记录时，此条件o.orderID=r.orderID无效，它会忽略该数据。执行左联接将解决此问题。

select 
o.orderDate, 
sum(o.netAmount)-sum(case when cr.totalAmount is null then 0 else cr.totalAmount end)
from 
Orders o 
left join 
CustomerReturn cr
on 
o.orderID = cr.orderID
group by 
o.orderDate

左联接将导致cr.totalAmount具有空值，以防o.orderID=r.orderID不匹配，则我们使用此部分；case when cr.totalAmount is null then 0 else cr.totalAmount end来修复该null问题。

Answer 2

因为您是在日期加入的，所以您没有得到正确的答案，因为订单日期和退货日期可能有不同的月份。如果您提取月份，然后按照下面的查询进行求和，效果会更好，下面是演示。

select
o.mm as month,
sum(total_net_amount - total_amount) as total
from
(
select
month(orderDate) as mm,
sum(netAmount) as total_net_amount
from Orders
group by
month(orderDate)
) o
join
(
select
month(retDate) as mm,
sum(totalAmount) as total_amount
from CustomerReturn
group by
month(retDate)
) cr
on o.mm = cr.mm
group by
o.mm

输出：

*--------------*
|month | total |
*--------------*
| 5    |  775  |
| 4    |  1325 |
*--------------*

Answer 3

学习使用正确、明确、标准可读的JOIN语法。正如另一个答案所指出的，您想要一个LEFT JOIN。也就是说，编写逻辑的更简单方法是：

select o.orderDate, 
sum(o.netAmount)- coalesce(sum(cr.totalAmount, 0)) as net_amount
from Orders o left join 
CustomerReturn cr
on o.orderID = cr.orderID
group by o.orderDate;