mysql - 在SQL上联接3个表并检查条件

SelectClassicModels.Orders.CustomerNumber,
ClassicModels.CUSTOMERS.CUSTOMERNAME,
ClassicModels.Employees.LASTNAME,
ClassicModels.Employees.firstNAME,
ClassicModels.employees.EmployeeNumber
from ClassicModels.Orders

join
ClassicModels.Customers
on ClassicModels.Orders.CustomerNumber = ClassicModels.Customers.CUSTOMERNUMBER

join

ClassicModels.EMPLOYEES
on ClassicModels.Employees.EMPLOYEENUMBER = ClassicModels.CUSTOMERS.SalesRepEmployeeNumber

连接时这三个表工作得很好，但是当我尝试添加这些修饰符时，它们不工作

group by ClassicModels.Orders.CustomerNumber
having count(ClassicModels.Orders.CustomerNumber) < 4

最佳答案

首先，欢迎来到StackOverflow！
我已经用我最喜欢的工具重新格式化了你的SQL，如果你感兴趣的话，我可以提供一个链接。我还添加了别名，以帮助它更“可读”。（别名是FROM和JOIN子句中表名后面的小写位。）

SELECT orders.CustomerNumber,
       customers.CustomerName,
       employees.LastName,
       employees.FirstName,
       employees.EmployeeNumber

FROM CLASSICMODELS.ORDERS orders

JOIN CLASSICMODELS.CUSTOMERS customers
  ON orders.CustomerNumber = customers.CustomerNumber

JOIN CLASSICMODELS.EMPLOYEES employees
  ON employees.EmployeeNumber = customers.SalesRepEmployeeNumber

现在我们已经完成了。让我们添加GROUP BY和HAVING子句。
GROUP BY子句必须包含SELECT子句中使用的所有列。（我不知道为什么。我还没查过，但我只知道是这样的。:) )

SELECT orders.CustomerNumber,
       customers.CustomerName,
       employees.LastName,
       employees.FirstName,
       employees.EmployeeNumber

FROM CLASSICMODELS.ORDERS orders

JOIN CLASSICMODELS.CUSTOMERS customers
  ON orders.CustomerNumber = customers.CustomerNumber

JOIN CLASSICMODELS.EMPLOYEES employees
  ON employees.EmployeeNumber = customers.SalesRepEmployeeNumber

GROUP BY orders.CustomerNumber,
         customers.CustomerName,
         employees.LastName,
         employees.FirstName,
         employees.EmployeeNumber

现在应该可以了。然后你只需要在里面加上你的HAVING子句。

SELECT orders.CustomerNumber,
       customers.CustomerName,
       employees.LastName,
       employees.FirstName,
       employees.EmployeeNumber

FROM CLASSICMODELS.ORDERS orders

JOIN CLASSICMODELS.CUSTOMERS customers
  ON orders.CustomerNumber = customers.CustomerNumber

JOIN CLASSICMODELS.EMPLOYEES employees
  ON employees.EmployeeNumber = customers.SalesRepEmployeeNumber

GROUP BY orders.CustomerNumber,
         customers.CustomerName,
         employees.LastName,
         employees.FirstName,
         employees.EmployeeNumber

HAVING COUNT(orders.CustomerNumber) < 4

我还查看了您的查询，通过使用这样的查询，您可能会获得更快（更高效）的结果：

WITH CUSTOMERSWITHLESSTHANFOURORDERS
AS
(
  SELECT CUSTOMERNUMBER
  FROM CLASSICMODELS.ORDERS
  GROUP BY CUSTOMERNUMBER
  HAVING COUNT(CUSTOMERNUMBER) < 4
)
SELECT O.CUSTOMERNUMBER,
       C.CUSTOMERNAME,
       E.LASTNAME,
       E.FIRSTNAME,
       E.EMPLOYEENUMBER
FROM CUSTOMERSWITHLESSTHANFOURORDERS O
JOIN CLASSICMODELS.CUSTOMERS C
  ON O.CUSTOMERNUMBER = C.CUSTOMERNUMBER
JOIN CLASSICMODELS.EMPLOYEES E
  ON E.EMPLOYEENUMBER = C.SALESREPEMPLOYEENUMBER;

它使用所谓的“公共表表达式”，基本上只是隔离查询的一部分。它可能会更有效率，因为它将尝试在更少的数据上分组，因此它可能更快。要小心，因为里面有很多“可能”，因为我不知道MySQL数据库中是如何设置各种不同的东西的。
祝你好运！