MySQL JOIN/WHERE/GROUP BY查询报错排查：按销售代表和公司统计订单总额

需求说明 #

需要统计每个公司中每位销售代表的订单总金额，涉及三张数据表：invoice、orders、salesteam。

表结构 #

invoice表 #

Order_Id,Date,Meal_Id,Company_Id,Date_of_Meal,Participants,Meal_Price,Type_of_Meal
839FKFW2LLX4LMBB,27-05-2016,INBUX904GIHI8YBD,LJKS5NK6788CYMUU,2016-05-31 07:00:00+02:00,['David Bishop'],469,Breakfast
97OX39BGVMHODLJM,27-09-2018,J0MMOOPP709DIDIE,LJKS5NK6788CYMUU,2018-10-01 20:00:00+02:00,['David Bishop'],22,Dinner
041ORQM5OIHTIU6L,24-08-2014,E4UJLQNCI16UX5CS,LJKS5NK6788CYMUU,2014-08-23 14:00:00+02:00,['Karen Stansell'],314,Lunch
YT796QI18WNGZ7ZJ,12-04-2014,C9SDFHF7553BE247,LJKS5NK6788CYMUU,2014-04-07 21:00:00+02:00,['Addie Patino'],438,Dinner
6YLROQT27B6HRF4E,28-07-2015,48EQXS6IHYNZDDZ5,LJKS5NK6788CYMUU,2015-07-27 14:00:00+02:00,['Addie Patino' 'Susan Guerrero'],690,Lunch
AT0R4DFYYAFOC88Q,21-07-2014,W48JPR1UYWJ18NC6,LJKS5NK6788CYMUU,2014-07-17 20:00:00+02:00,['David Bishop' 'Susan Guerrero' 'Karen Stansell'],181,Dinner
2DDN2LHS7G85GKPQ,29-04-2014,1MKLAKBOE3SP7YUL,LJKS5NK6788CYMUU,2014-04-30 21:00:00+02:00,['Susan Guerrero' 'David Bishop'],14,Dinner
FM608JK1N01BPUQN,08-05-2014,E8WJZ1FOSKZD2MJN,36MFTZOYMTAJP1RK,2014-05-07 09:00:00+02:00,['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoagland' 'Michael White'],320,Breakfast

orders表 #

Order_Id,Company_Id,Company_Name,Date,Order_Value,Converted
80EYLOKP9E762WKG,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,18-02-2017,4875,1
TLEXR1HZWTUTBHPB,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,30-07-2015,8425,0
839FKFW2LLX4LMBB,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,27-05-2016,4837,0
97OX39BGVMHODLJM,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,2018-09-27,343,0
5T4LGH4XGBWOD49Z,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,2016-01-14,983,0
041ORQM5OIHTIU6L,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,2014-08-24,4185,0
8QUW0UXQ3XHIL56W,LJKS5NK6788CYMUU,Chimera-Chasing Casbah,2018-09-06,3186,0

salesteam表 #

Sales_Rep, Sales_Rep_Id, Company_Name, Company_Id
Jessie Mcallister,97UNNAT790E0WM4N,Chimera-Chasing Casbah,LJKS5NK6788CYMUU
Jessie Mcallister,97UNNAT790E0WM4N,Two-Mile Grab,H3JRC7XX7WJAD4ZO
Jessie Mcallister,97UNNAT790E0WM4N,Three-Men-And-A-Helper Congo'S,HB25MDZR0MGCQUGX
Jessie Mcallister,97UNNAT790E0WM4N,Paleocortical Boatloads,NUQS9SHQH6IU92V8
Jessie Mcallister,97UNNAT790E0WM4N,Editorial Paintbrush,PQ79N68UEQ9FFCPU
Jessie Mcallister,97UNNAT790E0WM4N,Victorian Aim,93DU98KT3NZCOW58
Jessie Mcallister,97UNNAT790E0WM4N,Industrial Opinions,BQMPJF0W2Z2E0PEW
Lois Bowers,RRD2R9XMAJDP7TUY,Fantastic Re-Enactments,W2X6NP1JBOKWCO33
Lois Bowers,RRD2R9XMAJDP7TUY,Infamous Inoculation,D459BZ8Z7N1KAFGU
Lois Bowers,RRD2R9XMAJDP7TUY,Simple-Seeming Tenure,MR6NETSKD2PSN54L

错误语句及报错 #

错误查询语句 #

SELECT s.Sales_Rep, SUM(o.Order_Value) AS Total_Order_Value
FROM salesteam s
JOIN orders o ON s.Company_Id = o.Company_Id
JOIN invoice i ON o.Order_Id = i.Order_Id
WHERE DISTINCT s.Company_Name
GROUP BY s.Sales_Rep;

报错信息 #

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT s.Company_Name
                            GROUP BY s.Sales_Rep' at line 6")

错误原因分析 #

1. 语法错误：WHERE DISTINCT s.Company_Name 不符合SQL语法规范，DISTINCT只能用于修饰SELECT的返回结果，不能出现在WHERE子句中。
2. 逻辑错误：需求是统计每个公司中每位销售代表的金额，但原语句仅按s.Sales_Rep分组，会将同一销售代表负责的多个公司的订单金额合并，无法区分不同公司的数据。

修正后的SQL语句 #

SELECT 
    s.Company_Name,
    s.Sales_Rep,
    SUM(o.Order_Value) AS Total_Order_Value
FROM salesteam s
JOIN orders o ON s.Company_Id = o.Company_Id
JOIN invoice i ON o.Order_Id = i.Order_Id
GROUP BY s.Company_Id, s.Company_Name, s.Sales_Rep;

关键修正点说明 #

• 新增Company_Name到SELECT和GROUP BY中，确保统计维度是公司+销售代表，满足需求。
• 移除了错误的WHERE DISTINCT语句，修复语法问题。
• 若需保留无对应发票的订单，可将JOIN invoice i改为LEFT JOIN invoice i，同时将SUM(o.Order_Value)改为SUM(IFNULL(o.Order_Value, 0))避免空值影响统计结果。

可选：处理订单重复情况 #

如果存在一个订单对应多条发票记录的情况，需要先对订单去重，避免金额重复计算：

SELECT 
    s.Company_Name,
    s.Sales_Rep,
    SUM(o.Order_Value) AS Total_Order_Value
FROM salesteam s
JOIN (
    SELECT DISTINCT Order_Id, Company_Id, Order_Value 
    FROM orders
) o ON s.Company_Id = o.Company_Id
JOIN invoice i ON o.Order_Id = i.Order_Id
GROUP BY s.Company_Id, s.Company_Name, s.Sales_Rep;

内容的提问来源于stack exchange，提问作者c200402