时间戳

首页 > 解决方案 > 如何使用sql组合来自两个不同表的结果

sql - 如何使用sql组合来自两个不同表的结果

问题描述

我有四张桌子，

  1. 顾客
  2. 订单
  3. 订单项
  4. 销售量

“订单”表包含订单“完成”或“处理中”的状态当订单完成时，我将销售信息存储在销售表中，将订单标记为完成，并将总金额和余额存储在销售表中。现在我想从订单表中获取客户名称，并从销售表中获取每个客户的余额。我已使用此查询，但无法获得所需的结果。

输出是：

电流输出

所需的输出是：

期望的输出

Select c.Name As CustomerName, c.ContactNumber AS CustomerContactNumber, c.Location AS CustomerAddress, Sum(oi.Amount) As Amount 
from OrderItems oi 
  inner join Orders o on oi.OrderId=o.Id 
  inner join Customers c on o.CustomerId=c.Id 
Where o.Status=='Processing' Group By o.CustomerId

UNION

Select c.Name As CustomerName, c.ContactNumber AS CustomerContactNumber, c.Location AS CustomerAddress,SUM(s.Balance) As Balance 
from Sales s 
  inner join Customers c on s.CustomerId=c.Id 
Group By s.CustomerId

标签： sqlsqlite

解决方案

您不需要union，您可以使用left join，加入sum(sales)您的客户orders表。

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 
left 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.CustomerId
where o.Status=='Processing' 
Group By c.CustomerId, c.ContactNumber, c.Location

推荐阅读