多表关联求和计数并按客户/公司名称合并排序的SQL查询问题
复杂SQL统计问题处理
涉及的表及数据
Orders表
OrderId ClientId CompanyId -------------------------------- 25 2 NULL 26 NULL 1 27 3 NULL 28 3 NULL 29 NULL 3
Billing表与Forwards表
Billing表 OrderId Amount PaidFull -------------------------- 25 $100 False 26 $50 False 27 $100 False 28 $100 False 29 $100 True Forwards表 OrderId Amount PaidFull --------------------------- 26 $25 False 27 $50 False 27 $50 False 29 $50 False
Client表与Company表
Client表 ClientId ClientName ----------------------- 1 AClient 2 BClient 3 CClient Company表 CompanyId CompanyName --------------------------- 1 ACompany 2 BCompany 3 CCompany
需求说明
统计每个客户或公司的Billing总金额、记录数,以及Forwards总金额、记录数,最终按客户名称与公司名称合并后的字母序排序,预期输出如下:
Billing Qty Forwards Qty Client/Company Name -------------------------------------------------- $50 1 $25 1 ACompany $100 1 $0 0 BClient $200 2 $100 2 CClient $0 0 $50 1 CCompany
原有SQL问题
原有查询存在三处问题:
- 过滤逻辑错误:Where条件拼写错误
b.FaidFull,且会过滤掉Billing、Forwards只有一方有符合条件数据的订单 - 重复计算问题:直接关联Billing和Forwards表,一对多关联会导致统计数值翻倍
- 排序逻辑错误:分开按客户名、公司名排序,没有将两者合并统一排序
原有SQL代码:
Select Sum(b.Amount) As TotalAmount, Count(b.Amount) As Count1, Sum(f.Amount) As TotalAmountf, Count(f.Amount) As Count2, d.ClientName, c.CompanyName From Orders a Left Join Billing b On a.OrderId = b.OderId And b.PaidFull = 'False' Left Join Forwards f On a.OrderId = f.OderId And f.PaidFull = 'False' Left Join Client d On a.ClientId = d.ClientId Left Join Company c On a.CompanyId = c.CompanyId Where (b.FaidFull = 'False' Or f.PaidFull = 'False') Group by ClientName, CompanyName Order by ClientName, CompanyName
修正后SQL
先分别预聚合Billing和Forwards表的单订单统计结果,再关联订单、客户、公司表,合并名称字段实现统一排序:
SELECT COALESCE(SUM(b.billing_total), 0) AS Billing, COALESCE(SUM(b.billing_count), 0) AS Qty, COALESCE(SUM(f.forwards_total), 0) AS Forwards, COALESCE(SUM(f.forwards_count), 0) AS Qty, COALESCE(cl.ClientName, co.CompanyName) AS `Client/Company Name` FROM Orders o -- 预聚合Billing表,过滤已结清订单 LEFT JOIN ( SELECT OrderId, SUM(Amount) AS billing_total, COUNT(*) AS billing_count FROM Billing WHERE PaidFull = 'False' GROUP BY OrderId ) b ON o.OrderId = b.OrderId -- 预聚合Forwards表,过滤已结清订单 LEFT JOIN ( SELECT OrderId, SUM(Amount) AS forwards_total, COUNT(*) AS forwards_count FROM Forwards WHERE PaidFull = 'False' GROUP BY OrderId ) f ON o.OrderId = f.OrderId LEFT JOIN Client cl ON o.ClientId = cl.ClientId LEFT JOIN Company co ON o.CompanyId = co.CompanyId GROUP BY `Client/Company Name` -- 合并名称统一排序 ORDER BY `Client/Company Name` ASC;
内容的提问来源于stack exchange,提问作者mlg74
相关产品推荐
相关产品推荐

