如何在甲骨文CMD中使用"not in"运算符？

Question

我正在使用这个代码:

create or replace view VIEW_MAXMIN as
select c.country_name,
max(salary) max_salary,
min(salary) min_salary
from   employees e,
departments d,
locations l,
countries c
where  e.department_id = d.department_id
and    d.location_id = l.location_id
and    l.country_id = c.country_id = not in(select country_name
from   countries
where  country_name = 'Mexico');

但总是错误地说'SQL命令没有正确结束'

Answer 1

您可以将连接从传统的Oracle逗号连接更改为ANSI连接，然后看起来您希望名称不是Mexico的国家/地区。

同样，如果您正在聚合列，并且有一个未聚合的列，那么您需要使用GROUP BY:

create or replace view VIEW_MAXMIN as
select c.country_name,
max(salary) max_salary,
min(salary) min_salary
from   employees e
INNER JOIN departments d
ON (e.department_id = d.department_id)
INNER JOIN locations l
ON (d.location_id = l.location_id)
INNER JOIN countries c
ON (l.country_id = c.country_id)
where  c.country_name != 'Mexico'
GROUP BY c.country_name;

Answer 2

看起来你得到的错误是由于-

l.country_id = c.country_id = not in(select country_name
from   countries
where  country_name = 'Mexico');

在一个过滤器语句中有两个相等的条件，比如a = b = c仅-l.country_id = c.country_id就足够了，country_name != 'Mexico'必须是一个单独的条件