时间戳

首页 > 解决方案 > 'WITH' 子句不适用于 in 运算符

sql - 'WITH' 子句不适用于 in 运算符

问题描述

我有一个简单的查询：

with MaxSal AS (SELECT MAX(Salary), DepartmentId FROM Employee GROUP BY DepartmentId)
SELECT Department.Name AS "Department", Employee.Name AS "Employee", Employee.Salary AS "Salary" FROM Employee
JOIN Department ON Department.Id = Employee.DepartmentId
WHERE (Employee.Salary, Employee.DepartmentId) in MaxSal;

它应该创建一个MaxSal临时表，然后在该WHERE部分中它应该检查是否Salary, DepId在所述表内。不幸的是，这个查询给了我 ORA-00920: invalid relational operator我猜是指in操作员。如果我直接放置子查询而不是先将其放入临时表中，那么一切都会按预期工作。
有人可以告诉我为什么它不适用于该WITH条款吗？

标签： sqloraclesubquerycommon-table-expression

解决方案

您需要一个表引用来引用 CTE，而这又需要一个FROM子句。您可以使用子查询做您想做的事

WITH MaxSal AS (
      SELECT MAX(Salary) as max_salary, DepartmentId
      FROM Employee
      GROUP BY DepartmentId
     )
SELECT d.Name AS Department, e.Name AS Employee, 
       e.Salary AS Salary
FROM Employee e JOIN
     Department d
     ON d.Id = e.DepartmentId
WHERE (e.Salary, e.DepartmentId) in (SELECT max_salary, DepartmentId) FROM MaxSal);

也就是说，RANK()要简单得多：

SELECT Department, Name AS Employee, Salary
FROM (SELECT e.*, d.Name as Department,
             RANK() OVER (PARTITION BY d.id ORDER BY e.Salary DESC) as seqnum
      FROM Employee e JOIN
           Department d
           ON d.Id = e.DepartmentId
     ) ed
WHERE seqnum = 1;

推荐阅读