Oracle SQL - выберите внутри выбора (в той же таблице!)
Я постараюсь объяснить, чего я пытаюсь достичь быстро, так как я не знаю, как объяснить это иначе!
У нас есть таблица, которая показывает всю историю занятости для всех сотрудников, я хочу, чтобы «Start_Date» текущего поста («Current_Flag» = «Y»). Кроме того, я хочу, чтобы "End_Date" сообщения до этого (собирался отфильтровать по текущему флагу, отсортировать по дате окончания и просто захватить верхний)
Так или иначе, вот мой код:
SELECT "Gc_Staff_Number",
"Start_Date",
(SELECT "End_Date"
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
AND ROWNUM = 1
AND "Employee_Number" = "Employment_History"."Employee_Number"
ORDER BY "End_Date" ASC)
FROM "Employment_History"
WHERE "Current_Flag" = 'Y'
Любые предложения о том, как заставить это работать, были бы фантастическими, надеюсь, что вышеизложенное имеет некоторый смысл - если честно, запрос в настоящий момент даже не будет работать, который действительно отстой, хм.
(правка: О! Я пишу это для запроса существующей системы ... которая по какой-то причине имеет все глупые двойные кавычки вокруг таблицы и имен полей, вздох!)
5 ответов
Это именно тот сценарий, когда аналитика приходит на помощь.
С учетом этих тестовых данных:
SQL> select * from employment_history
2 order by Gc_Staff_Number
3 , start_date
4 /
GC_STAFF_NUMBER START_DAT END_DATE C
--------------- --------- --------- -
1111 16-OCT-09 Y
2222 08-MAR-08 26-MAY-09 N
2222 12-DEC-09 Y
3333 18-MAR-07 08-MAR-08 N
3333 01-JUL-09 21-MAR-09 N
3333 30-JUL-10 Y
6 rows selected.
SQL>
Встроенное представление с аналитической функцией LAG() дает правильный ответ:
SQL> select Gc_Staff_Number
2 , start_date
3 , prev_end_date
4 from (
5 select Gc_Staff_Number
6 , start_date
7 , lag (end_date) over (partition by Gc_Staff_Number
8 order by start_date )
9 as prev_end_date
10 , current_flag
11 from employment_history
12 )
13 where current_flag = 'Y'
14 /
GC_STAFF_NUMBER START_DAT PREV_END_
--------------- --------- ---------
1111 16-OCT-09
2222 12-DEC-09 26-MAY-09
3333 30-JUL-10 21-MAR-09
SQL>
Встроенное представление имеет решающее значение для получения правильного результата. В противном случае фильтр по CURRENT_FLAG удаляет предыдущие строки.
Меня немного смущают цитаты, однако приведенные ниже должны работать для вас:
SELECT "Gc_Staff_Number",
"Start_Date", x.end_date
FROM "Employment_History" eh,
(SELECT "End_Date"
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
AND ROWNUM = 1
AND "Employee_Number" = eh.Employee_Number
ORDER BY "End_Date" ASC) x
WHERE "Current_Flag" = 'Y'
SELECT eh."Gc_Staff_Number",
eh."Start_Date",
MAX(eh2."End_Date") AS "End_Date"
FROM "Employment_History" eh
LEFT JOIN "Employment_History" eh2
ON eh."Employee_Number" = eh2."Employee_Number" and eh2."Current_Flag" != 'Y'
WHERE eh."Current_Flag" = 'Y'
GROUP BY eh."Gc_Staff_Number",
eh."Start_Date
Это то, что я бы использовал с помощью функции LAG для:
SELECT eh.gc_staff_number,
eh.start_date,
LAG(eh.end_date) OVER (PARTITION BY eh.gc_staff_number
ORDER BY eh.end_date) AS prev_end_date
FROM EMPLOYMENT_HISTORY eh
WHERE eh.current_flag = 'Y'
Если вы хотите заглянуть на строку впереди, вы должны использовать функцию LEAD .
Совместимость:
Насколько мне известно, поддерживается 9i +, но я не подтверждал, что 8i поддерживается, как указано в документации.
LEAD и LAG, наконец, стали ANSI, но в настоящее время их поддерживают только Oracle и PostgreSQL v8.4 + .
SELECT "Gc_Staff_Number",
"Start_Date",
(SELECT "End_Date"
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
AND ROWNUM = 1
AND "Employee_Number" = "Employment_History"."Employee_Number"
ORDER BY "End_Date" ASC)
FROM "Employment_History"
WHERE "Current_Flag" = 'Y'
К вашему сведению, в этом случае ROWNUM = 1 оценивается до ORDER BY, поэтому внутренний запрос будет сортировать общую сумму (максимум) одной записи.
Если вы действительно ищете самую раннюю end_date для данного сотрудника (где current_flag <> 'Y'), это то, что вы ищете?
SELECT "Gc_Staff_Number",
"Start_Date",
eh.end_date
FROM "Employment_History" eh
LEFT OUTER JOIN -- in case the current record is the only record...
(SELECT "Employee_Number"
, MIN("End_Date") as end_date
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
GROUP BY "Employee_Number"
) emp_end_date
ON eh."Employee_Number" = emp_end_date."Employee_Number"
WHERE eh."Current_Flag" = 'Y'