Подзапрос, который обращается к полям основной таблицы в сочетании с предложением LIMIT в Oracle SQL.

У меня есть таблица «Пользователи» и таблица «Задачи». Задачи упорядочены по важности и назначаются в список задач пользователя. Задачи имеют статус: готовы или не готовы. Теперь я хочу перечислить всех пользователей с их самой важной задачей, которая также готова.

Интересное требование о том, что задачи для каждого пользователя сначала нужно отфильтровать и отсортировать, а потом выбрать самую важную. Вот что я придумал:

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

Как бы я решил проблему?


sql oracle subquery oracle11g correlated-subquery
person Yogu    schedule 22.12.2016    source источник
comment
Что вы имеете в виду под WHERE task.isready? В базе данных Oracle нет логических значений.   -  person mathguy    schedule 22.12.2016

Ответы (3)


arrow_upward
1
arrow_downward

Пожалуйста, попробуйте с аналитической функцией:

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;
person Kacper    schedule 22.12.2016

arrow_upward
2
arrow_downward

Один обходной путь для этого использует 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;
person Gordon Linoff    schedule 22.12.2016
comment
Это MAX(t.description) выглядит странно, или это необходимо для обхода? В противном случае это выглядит более кратким, чем ответ Кацпера. Знаете ли вы, отличается ли производительность? - person Yogu; 22.12.2016
comment
@Йогу. . . На самом деле это выглядит странно. MAX() ничего не делает, потому что DENSE_RANK FIRST соответствует только одному значению. . .so MIN() и MAX() сделают то же самое. Я никогда не использовал KEEP в контексте, где могут быть возвращены значения из нескольких строк; но я предполагаю, что функция агрегирования, вероятно, при некоторых обстоятельствах используется. - person Gordon Linoff; 22.12.2016
comment
@GordonLinoff - в плотном_ранге сначала могут возникнуть ничьи, если упорядочение по важности не является строгим. Могут быть три задачи одинаковой, высшей важности. Затем MAX выберет задачу с последним описанием в алфавитном порядке. MIN выберет первое описание в алфавитном порядке — из числа наиболее важных задач. - person mathguy; 23.12.2016

arrow_upward
1
arrow_downward

Вот решение, которое использует агрегацию, а не аналитические функции. Вы можете сравнить это с решением аналитических функций, чтобы увидеть, какое из них быстрее; во многих случаях агрегированные запросы (немного) быстрее, но это зависит от ваших данных, использования индекса и т. д.

Это решение похоже на то, что пытался сделать Гордон. Я не знаю, почему он написал это, используя коррелированный подзапрос вместо прямого соединения (и не знаю, сработает ли это - я никогда не видел, чтобы функция FIRST/LAST использовалась с такими коррелированными подзапросами).

Это может работать не совсем правильно, если в столбце importance может быть NULL - тогда вам нужно будет добавить nulls first после t.importance и перед ). Примечание: max(t.description) необходимо, потому что могут быть связи по «важности» (две задачи с одинаковой, наивысшей важностью для данного пользователя). В этом случае необходимо выбрать одну задачу. Если упорядочение по важности является строгим (без связей), то MAX() ничего не делает, поскольку выбирает 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
person mathguy    schedule 22.12.2016