刚接触SQL不太会用EXISTS
这有道题怎样解:
某公司SQL Server 2000一数据库中的Employees表用于存放所有员工的信息,Orders表用于存放所有的订单信息,Orders表有一外部键引用Employees表的主键,两表的关系如下图所示:
现在公司想统计一下最近一个月内没有任何销售订单的员工,则下列SQL语句中符合要求的有( )。(选择一项)
a) SELECT LastName, FirstName
FROM Employees AS e
WHERE EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate > GetDate()-30)
b) SELECT LastName, FirstName
FROM Employees AS e
WHERE (SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate < GetDate()-30) > 0
c) SELECT LastName, FirstName
FROM Employees AS e
WHERE (SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate > GetDate()-30) < 0
d) SELECT LastName, FirstName
FROM Employees AS e
WHERE EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate < GetDate()-30)是不是应该选D?
这有道题怎样解:
某公司SQL Server 2000一数据库中的Employees表用于存放所有员工的信息,Orders表用于存放所有的订单信息,Orders表有一外部键引用Employees表的主键,两表的关系如下图所示:
现在公司想统计一下最近一个月内没有任何销售订单的员工,则下列SQL语句中符合要求的有( )。(选择一项)
a) SELECT LastName, FirstName
FROM Employees AS e
WHERE EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate > GetDate()-30)
b) SELECT LastName, FirstName
FROM Employees AS e
WHERE (SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate < GetDate()-30) > 0
c) SELECT LastName, FirstName
FROM Employees AS e
WHERE (SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate > GetDate()-30) < 0
d) SELECT LastName, FirstName
FROM Employees AS e
WHERE EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate < GetDate()-30)是不是应该选D?
FROM Employees AS e
WHERE NOT EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate > GetDate()-30)
D是查出距今天一个月以前有销售订单的人员
所在没有一个符合要求
Orders.OrderDate > GetDate()-30 得出来得是有销售得员工吧
谢谢各位!
FROM Employees AS e
WHERE EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate < GetDate()-30)----------------
一个月前有销售订单 != 最近一个月内没有任何销售订单除非,一個EmployeeID對應到Orders中只有一條紀錄。
FROM Employees AS e
WHERE NOT EXISTS
(SELECT * FROM Orders
WHERE Orders.EmployeeID=e.EmployeeID
AND Orders.OrderDate > GetDate()-30)