访问Oracle SQL中限制条款的主表字段的子查询

Question

我得到了一个表用户和一个表任务。任务是按重要性订购的，并分配给用户的任务列表。任务具有状态：准备就绪或不准备就绪。现在，我想列出所有也准备好任务的用户。

有趣的要求是，每个用户的任务首先需要过滤和分类，然后选择最重要的任务。这就是我想到的：

SELECT Users.name,
    (SELECT *
        FROM (SELECT Tasks.description
              FROM Tasks
              WHERE Tasks.taskListCode = Users.taskListCode AND Tasks.isReady
              ORDER BY Tasks.importance DESC)
        WHERE rownum = 1
    ) AS nextTask
    FROM Users

但是，这导致错误

ORA-00904: "Users"."taskListCode": invalid identifier

我认为，原因是Oracle不支持将子征服与多个级别的深度相关联。但是，我需要两个级别，以便我可以执行WHERE rownum = 1。

我也没有相关的子查询尝试：

SELECT Users.name, Task.description
FROM Users
LEFT JOIN Tasks nextTask ON
    nextTask.taskListCode = Users.taskListCode AND
    nextTask.importance = MAX(
        SELECT tasks.importance 
        FROM tasks
        WHERE tasks.isReady
        GROUP BY tasks.id
    )

这导致错误

ORA-00934: group function is not allowed here

我将如何解决问题？

Answer 1

使用keep：

SELECT u.name,
       (SELECT MAX(t.description) KEEP (DENSE_RANK FIRST ORDER BY T.importance DESC)
        FROM Tasks t
        WHERE t.taskListCode = u.taskListCode AND t.isReady
       ) as nextTask
FROM Users u;

Answer 2

请尝试使用分析功能：

with tp as (select t.*, row_number() over (partition by taskListCode order by importance desc) r 
            from tasks t 
            where isReady = 1 /*or 'Y' or what is positive value here*/)
select u.name, tp.description 
  from users u left outer join tp on (u.taskListCode = tp.taskListCode) 
  where tp.r = 1;

Answer 3

这是一种使用聚合而不是分析函数的解决方案。您可能需要针对分析功能解决方案运行此操作，以查看哪个更快；在许多情况下，汇总查询速度更快，但取决于您的数据，索引使用等。

此解决方案类似于戈登（Gordon）尝试做的事情。我不知道他为什么使用相关的子查询而不是直接加入（并且不知道它是否有效） - 我从未见过与这样的相关子征服使用的第一个/最后一个功能）。

如果importance列中可能有NULL，则可能无法正常工作 - 那么您需要在t.importance之后和)之前添加nulls first。注意：需要max(t.description)，因为可能存在"重要性"（对于给定用户而言相同，最重要的两个任务）。在这种情况下，必须选择一个任务。如果按重要性订购是严格的（没有纽带），则MAX()无需执行任何操作，因为它选择了最大值，而不是一组一个值，但是编译器不知道事先需要MAX()。

。

select u.name, 
       max(t.description) keep (dense_rank last order by t.importance) as descr
from   users u left outer join tasks t on u.tasklistcode = t.tasklistcode
where  t.isready = 'Y'
group by u.name