时间戳

首页 > 解决方案 > mysql查询中多个连接中的where子句

mysql - mysql查询中多个连接中的where子句

问题描述

我有以下表格：- - 员工 - 订单 - OrderDetails

Employees
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| EmployeeID      | int          | NO   | PRI | NULL    |       |
| LastName        | varchar(20)  | YES  |     | NULL    |       |
| FirstName       | varchar(10)  | YES  |     | NULL    |       |
| Title           | varchar(30)  | YES  |     | NULL    |       |
| TitleOfCourtesy | varchar(25)  | YES  |     | NULL    |       |
| BirthDate       | datetime     | YES  |     | NULL    |       |
| HireDate        | datetime     | YES  |     | NULL    |       |
| Address         | varchar(60)  | YES  |     | NULL    |       |
| City            | varchar(15)  | YES  |     | NULL    |       |
| Region          | varchar(15)  | YES  |     | NULL    |       |
| PostalCode      | varchar(10)  | YES  |     | NULL    |       |
| Country         | varchar(15)  | YES  |     | NULL    |       |
| HomePhone       | varchar(24)  | YES  |     | NULL    |       |
| Extension       | varchar(4)   | YES  |     | NULL    |       |
| Notes           | mediumtext   | YES  |     | NULL    |       |
| ReportsTo       | int          | YES  |     | NULL    |       |
| PhotoPath       | varchar(255) | YES  |     | NULL    |       |
| Salary          | float        | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
Orders
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| OrderID        | int           | NO   | PRI | NULL    |       |
| CustomerID     | varchar(5)    | YES  |     | NULL    |       |
| EmployeeID     | int           | YES  |     | NULL    |       |
| OrderDate      | datetime      | YES  |     | NULL    |       |
| RequiredDate   | datetime      | YES  |     | NULL    |       |
| ShippedDate    | datetime      | YES  |     | NULL    |       |
| ShipVia        | int           | YES  |     | NULL    |       |
| Freight        | decimal(10,4) | YES  |     | NULL    |       |
| ShipName       | varchar(40)   | YES  |     | NULL    |       |
| ShipAddress    | varchar(60)   | YES  |     | NULL    |       |
| ShipCity       | varchar(15)   | YES  |     | NULL    |       |
| ShipRegion     | varchar(15)   | YES  |     | NULL    |       |
| ShipPostalCode | varchar(10)   | YES  |     | NULL    |       |
| ShipCountry    | varchar(15)   | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
OrdreDetails
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| OrderID   | int           | YES  | MUL | NULL    |       |
| ProductID | int           | YES  | MUL | NULL    |       |
| UnitPrice | decimal(10,4) | YES  |     | NULL    |       |
| Quantity  | smallint      | YES  |     | NULL    |       |
| Discount  | double        | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

我的问题是 给出员工的标识符、姓名和总销售额，按员工标识符为已销售 70 多种不同产品的员工排序

我写了这个查询

select t1.EmployeeID as Identifier,
       concat(t1.FirstName, ' ', t1.LastName) as Name,
       (select count(*) from orders t4 where t4.EmployeeID = identifier) as Total_Sales,
       count(distinct(t3.ProductID)) as Total_unique_products
  from Employees t1
 inner join Orders t2
    on t1.EmployeeID = t2.EmployeeID
 inner join orderdetails t3
    on t2.OrderID = t3.OrderID
 group by t1.EmployeeID
 order by t1.EmployeeID;

我还想只显示 Total_unique_products 大于 70 的那些；我该怎么做？

标签： mysqlsql

解决方案

您需要一个having子句、concat(t1.FirstName, ' ', t1.LastName)添加到group by子句的列和重新格式化count(distinct...)表达式（内括号是多余的）。

Aleft join将是更好的选择，因为可能存在不匹配的记录。

不需要相关子查询。所以把你的转换成这个：

select e.EmployeeID as Identifier,
       concat(e.FirstName, ' ', e.LastName) as Name,
       count(o.ID) as Total_Sales,
       count(distinct od.ProductID) as Total_unique_products
  from Employees e
  left join Orders o
    on e.EmployeeID = o.EmployeeID
  left join orderdetails od
    on o.OrderID = od.OrderID
 group by e.EmployeeID, concat(e.FirstName, ' ', e.LastName)
having count(distinct od.ProductID) > 70
 order by e.EmployeeID;

推荐阅读