where子句

fhg3lkii 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(264)

我有追随者tables:- - 员工-订单-订单详情

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 di的员工的erﬀ不同产品
我写了这个查询

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;

我也只想展示那些产品总数大于70的地方；我该怎么做？

sql mysql

来源：https://stackoverflow.com/questions/61950211/where-clause-in-multiple-joins-in-mysql-query

2条答案

按热度按时间

zfciruhq1#

你需要一个 having 条款， concat(t1.FirstName, ' ', t1.LastName) 列添加到 group by 子句，并重新格式化 count(distinct...) 表达式（内圆括号是多余的）。
一 left 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;

赞(0）回复(0）举报 2021-08-13

vngu2lb82#

主要用途 where 要筛选特定员工，还可以使用 having 筛选总产品数大于70的记录

select 
    t1.EmployeeID as Identifier, 
    concat(t1.FirstName, ' ', t1.LastName) as Name, 
    count(t2.OrderID) 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 
where t2.EmployeeID = identifier
group by t1.EmployeeID 
having count(distinct(t3.ProductID)) >70
order by t1.EmployeeID;

赞(0）回复(0）举报 2021-08-13

我来回答

where子句

2条答案

相关问题

热门标签

最新问答