You need to enable JavaScript to run this app. 多表关联求和计数并按客户/公司名称合并排序的SQL查询问题
优惠活动
大模型
产品
解决方案
定价
更多

多表关联求和计数并按客户/公司名称合并排序的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问题

原有查询存在三处问题:

  1. 过滤逻辑错误:Where条件拼写错误b.FaidFull,且会过滤掉Billing、Forwards只有一方有符合条件数据的订单
  2. 重复计算问题:直接关联Billing和Forwards表,一对多关联会导致统计数值翻倍
  3. 排序逻辑错误:分开按客户名、公司名排序,没有将两者合并统一排序

原有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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.17 21:36:08