SQL:选择不包含特定值的组

I use Microsoft SQL Server Management Studio 2014 and I have these 3 tables:

我使用Microsoft SQL Server Management Studio 2014，我有这3个表:

EMPLOYEES

员工

EMPID | FIRSTNAME
  1   | JOHNNY
  2   | DWAYNE
  3   | TOM
  4   | CHRISTIAN
  5   | JACK
  6   | BRAD
  7   | ADAM
  8   | MATT
  9   | WILL
  10  | JIM

AIRCRAFTS

飞机

AID | NAME
 1  | BOEING 1
 2  | BOEING 2
 3  | BOEING 3
 4  | BOEING 4
 5  | AIRBUS 1
 6  | AIRBUS 2
 7  | LEARJET
 8  | DOUGLAS
 9  | JUMBO
 10 | ILYUSHIN

CERTIFIED

认证

EMPID | AID
  1   |  1
  1   |  2
  1   |  3
  1   |  4
  4   |  2
  4   |  3
  7   |  1
  7   |  2
  7   |  5
  7   |  6
  8   |  7
  8   |  8
  8   |  9
  2   |  10
  2   |  1
  2   |  9
  3   |  10
  5   |  8
  5   |  9

The concept is that there are 10 employees and 10 aircrafts. The CERTIFIED table determines which employee is authorized to pilot which aircrafts. Not all employees are pilots though. What I need is to somehow select all pilots who are not certified to use a Boeing. What I tried but did not work is the following:

这个概念是有10名员工和10架飞机。认证表确定哪个员工被授权驾驶哪架飞机。但并非所有员工都是飞行员。我需要的是，以某种方式选择所有没有获得波音认证的飞行员。我尝试过但没有成功的是:

SELECT DISTINCT FIRSTNAME
FROM EMPLOYEES
WHERE EMPID IN (SELECT EMPID
                FROM CERTIFIED
                WHERE AID NOT IN (SELECT AID FROM AIRCRAFTS WHERE NAME LIKE 'BOEING%'))

Which gives these results:

给出了这些结果:

JACK
MATT
TOM
ADAM
DWAYNE

This is wrong because according to the CERTIFIED table, ADAM and DWAYNE are authorized to pilot at least one Boeing.

这是错误的，因为根据认证表，亚当和德韦恩被授权驾驶至少一架波音。

Any help would be appreciated, thanks in advance!

如有任何帮助，敬请谅解，谢谢!

3 个解决方案

#1

Try this query...

试试这个查询…

SELECT employees.empid, Max(employees.firstname) AS FirstName 
FROM   certified 
       INNER JOIN employees ON employees.empid = certified.empid 
WHERE  certified.empid NOT IN (SELECT certified.empid 
                               FROM   certified 
                               INNER JOIN aircrafts ON aircrafts.aid = certified.aid 
                               WHERE  aircrafts.NAME LIKE 'BOEING%') 
GROUP  BY employees.empid

Demo: http://www.sqlfiddle.com/#!18/8f26d/27/0

演示:http://www.sqlfiddle.com/ ! 18/8f26d / 27/0

Result

结果

+-------+-----------+
| EMPID | FirstName |
+-------+-----------+
|     3 | TOM       |
|     5 | JACK      |
|     8 | MATT      |
+-------+-----------+

#2

You should use NOT IN the emp that are certified by Boeing in join with aircrafts

你应该使用不是在emp，由波音公司认证与飞机

SELECT DISTINCT FIRSTNAME
FROM EMPLOYEES
WHERE EMPID NOT IN (SELECT EMPID
                FROM CERTIFIED c 
                INNER JOIN AIRCRAFTS a ON on a.AID  = c.AID 
                WHERE a.NAME LIKE 'BOEING%')

#3

I think your query is giving any employee that is certified on a non-Boeing aircraft -- a subtly different set of people.

我认为你的问题是给任何在非波音飞机上获得认证的员工——一群有着微妙不同的人。

For your question, I would go for not exists:

对于你的问题，我认为不存在:

select e.*
from employees e
where not exists (select 1
                  from certified c join
                       aircrafts a
                       on c.aid = a.aid
                  where e.empid = c.empid and a.name like '%BOEING%'
                 );

Another approach -- if you just want the employee id -- uses aggregation and having

另一种方法——如果您只想要员工id——则使用聚合和拥有

select e.empid, e.firstname
from employees e join
     certified c
     on e.empid = c.empid join
     aircrafts a
     on c.aid = a.aid
group by e.empid, e.firstname
having sum(case when a.name like '%BOEING%' then 1 else 0 end) = 0;

I happen to like this method, because it generalizes very easily to other conditions -- such as flies Boeing but not Airbus or flies Learjet and Cessna.

我碰巧喜欢这种方法，因为它很容易推广到其他情况——比如飞波音而不是空中客车，或者飞里尔喷气式飞机和塞斯纳飞机。

#1