Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 exists и order by  [new]
grinn
Member

Откуда:
Сообщений: 20
Добрый день всем.
Задача - выбрать записи из tab1, у которых есть связанные записи в таблице tab2, но только c определенными условиям (condition = 'Y').
При этом нужно брать только последнюю запись из tab2.

Делаю так:
SELECT *
FROM   tab1
WHERE  EXISTS (SELECT 1
		FROM   (SELECT *
				FROM   tab2
				WHERE  tab2.id = tab1.id
				ORDER  BY tab2.date DESC) s1
		WHERE  ROWNUM = 1
			   AND s1.condition = 'Y');

Вроде по логике все верно. Ошибка:
Error
Недопустимый идентификатор tab1.id

То есть, во вложенном запросе s1 не видно tab1.
Как переделать запрос, чтоб сохранить логику?
18 июн 19, 08:19    [21910274]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
grinn,

В общем случае - inline-view не может быть коррелированным. Если нужна корреляция, то или LATERAL, или [CROSS | OUTER] APPLY
18 июн 19, 08:20    [21910275]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Elic
Member

Откуда:
Сообщений: 29980
Щукина Анна
Если нужна корреляция, то или LATERAL, или [CROSS | OUTER] APPLY
А если подумать?
+
RTFM FIRST/LAST
18 июн 19, 08:23    [21910277]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
grinn,

перепишите запрос через сложенные [not]exists.
выбрать из tab1все то, для чего существует в tab2 запись со статусом 'Y' в то время, как для TAB2 не существует записи после той, что со статусом 'Y'
18 июн 19, 08:23    [21910278]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Elic
А если подумать?
"А чо тут думать? Прыгать нужно!"(с) старый детский анекдот.
18 июн 19, 08:25    [21910281]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Elic
Member

Откуда:
Сообщений: 29980
Щукина Анна
"А чо тут думать? Прыгать нужно!"(с) старый детский анекдот.
Думать нужно всегда. Всё ещё жду адекватного ответа.
18 июн 19, 08:28    [21910283]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Щукина Анна
grinn,

В общем случае - inline-view не может быть коррелированным. Если нужна корреляция, то или LATERAL, или [CROSS | OUTER] APPLY

если есть LATERAL,APPLY то версия не древняя

в новых версиях запрос отрабатывает (не слетает)
with tab1 as (
select 1 id,'Anna' n from dual)
,tab2 as (
select 1 id,sysdate dat,'Y' condition from dual)
SELECT *
FROM   tab1 
WHERE  EXISTS (SELECT 1
		FROM   (SELECT *
				FROM   tab2
				WHERE  tab2.id = tab2.id
				ORDER  BY tab2.dat DESC) s1
		WHERE  ROWNUM = 1
			   AND s1.condition = 'Y');

ID	N
1	Anna


.....
stax
18 июн 19, 08:42    [21910289]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Stax,

не то скопипастил
with tab1 as (
select 1 id,'Anna2' n from dual)
,tab2 as (
select 1 id,sysdate dat,'Y' condition from dual)
SELECT *
FROM   tab1 
WHERE  EXISTS (SELECT 1
		FROM   (SELECT *
				FROM   tab2
				WHERE  tab2.id = tab1.id
				ORDER  BY tab2.dat DESC) s1
		WHERE  ROWNUM = 1
			   AND s1.condition = 'Y');


.....
stax
18 июн 19, 08:44    [21910290]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Stax,


with tab1 as (
select 1 id,'Anna2' n from dual)
,tab2 as (
select 1 id,sysdate dat,'Y' condition from dual)
SELECT *
FROM   tab1 
WHERE  EXISTS (SELECT 1
		FROM   (SELECT *
				FROM   tab2
				WHERE  tab2.id = tab1.id
				ORDER  BY tab2.dat DESC) s1
		WHERE  ROWNUM = 1
			   AND s1.condition = 'Y');

ORA-00904: "TAB1"."ID": invalid identifier

select * from v$version;

BANNER
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Но тут и LATERAL с APPLY недоступны, конечно же...
18 июн 19, 08:51    [21910294]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Dshedoo
Member

Откуда:
Сообщений: 393
grinn
Задача - выбрать записи из tab1, у которых есть связанные записи в таблице tab2, но только c определенными условиям (condition = 'Y').
При этом нужно брать только последнюю запись из tab2.


select * from tab1 t1 join tab2 t2 on t1.id = t2.id and t2.condition = 'Y'
where not exists (select 1 from tab2 t2_e where t2_e.id = t2.id and t2_e.date < t2.date)
18 июн 19, 09:08    [21910300]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Щукина Анна
Но тут и LATERAL с APPLY недоступны, конечно же...

без латерал

для не совсем древних версий
SQL> ed
Wrote file afiedt.buf

  1  with tab1 as (
  2  select 1 id,'Anna' n from dual)
  3  ,tab2 as (
  4  select 1 id,sysdate dat,'Y' condition from dual)
  5  SELECT *
  6  FROM   tab1
  7  WHERE  EXISTS (
  8    SELECT 1
  9    FROM  tab2
 10    WHERE  tab2.id = tab1.id
 11    having max(condition) KEEP (DENSE_RANK last ORDER BY dat)='Y'
 12*   )
SQL> /

        ID N
---------- ----
         1 Anna


.....
stax
18 июн 19, 09:11    [21910303]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Dshedoo
grinn
Задача - выбрать записи из tab1, у которых есть связанные записи в таблице tab2, но только c определенными условиям (condition = 'Y').
При этом нужно брать только последнюю запись из tab2.


select * from tab1 t1 join tab2 t2 on t1.id = t2.id and t2.condition = 'Y'
where not exists (select 1 from tab2 t2_e where t2_e.id = t2.id and t2_e.date < t2.date)

  1   with tab1 as (
  2   select 1 id,'Anna' n from dual union all
  3   select 2,'Jon' n from dual
  4   )
  5   ,tab2 as (
  6   select 1 id,sysdate dat,'Y' condition from dual union all
  7   select 2 id,sysdate-1 dat,'Y' condition from dual union all
  8   select 2 id,sysdate dat,'N' condition from dual )
  9  select * from tab1 t1 join tab2 t2 on t1.id = t2.id and t2.condition = 'Y'
 10* where not exists (select 1 from tab2 t2_e where t2_e.id = t2.id and t2_e.dat < t2.dat)
SQL> /

        ID N            ID DAT      C
---------- ---- ---------- -------- -
         1 Anna          1 18.06.19 Y
         2 Jon           2 17.06.19 Y

.....
stax
18 июн 19, 09:17    [21910306]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Dshedoo
Member

Откуда:
Сообщений: 393
Stax,

"c определенными условиям (condition = 'Y')" и "брать только последнюю запись из tab2" != "брать только последнюю запись из tab2 c определенными условиям (condition = 'Y')".

Но судя по селекту автора, в мой экзист стоит добавить условие по кондишену.
18 июн 19, 09:32    [21910316]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Dshedoo

Но судя по селекту автора, в мой экзист стоит добавить условие по кондишену.


имхо
етого мало

....
stax
18 июн 19, 09:37    [21910326]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Elic
Member

Откуда:
Сообщений: 29980
Stax
без латерал
  7  WHERE  EXISTS (
 11    having max(condition) KEEP (DENSE_RANK last ORDER BY dat)='Y'
Осталось чуть-чуть - избавиться от EXISTS
18 июн 19, 09:37    [21910328]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Elic
Stax
без латерал
  7  WHERE  EXISTS (
 11    having max(condition) KEEP (DENSE_RANK last ORDER BY dat)='Y'
Осталось чуть-чуть - избавиться от EXISTS

імхо
с EXISTS
1) красивее
2) понятние
3) если в tab1 много полей, удобнее

....
stax
18 июн 19, 09:41    [21910330]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Dshedoo
Member

Откуда:
Сообщений: 393
Stax
Dshedoo
Но судя по селекту автора, в мой экзист стоит добавить условие по кондишену.


имхо
етого мало

....
stax


И даты, конечно же, наоборот.
Утро добрым не бывает.

   with tab1 as (
   select 1 id,'Anna' n from dual union all
   select 2,'Jon' n from dual
   )
   ,tab2 as (
   select 1 id,sysdate dat,'Y' condition from dual union all
   select 2 id,sysdate-1 dat,'Y' condition from dual union all
   select 2 id,sysdate-2 dat,'Y' condition from dual union all
   select 2 id,sysdate-3 dat,'N' condition from dual union all
   select 2 id,sysdate-4 dat,'Y' condition from dual union all         
   select 2 id,sysdate-5 dat,'N' condition from dual union all
   select 2 id,sysdate-6 dat,'Y' condition from dual union all
   select 2 id,sysdate dat,'N' condition from dual )
  select * from tab1 t1 join tab2 t2 on t1.id = t2.id and t2.condition = 'Y'
  where not exists (select 1 from tab2 t2_e where t2_e.id = t2.id and t2_e.dat > t2.dat and t2_e.condition = t2.condition)
18 июн 19, 10:13    [21910349]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Dshedoo,

SQL> /

        ID N            ID DAT      C
---------- ---- ---------- -------- -
         1 Anna          1 18.06.19 Y
         2 Jon           2 17.06.19 Y


нужны дополнительные разяснения автора

я по другому понял постановку
для 2 (select 2 id,sysdate dat,'N') последнее состояние N, отбрасываем строку

зы
поясніть результат в
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
я не смог (туплю)

....
stax
18 июн 19, 10:47    [21910370]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Elic
Member

Откуда:
Сообщений: 29980
Stax
1) красивее
Нет. Это как агрегировать аналитикой.
Stax
2) понятние
Нет. Вопрос "Где последнее соостояние равно?" формулируется дословно как
(select max(...) keep ...) = 'Y'

Stax
3) если в tab1 много полей, удобнее
Про expression list не слышал?
18 июн 19, 10:48    [21910372]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Elic
Stax
1) красивее
Нет. Это как агрегировать аналитикой.
Stax
2) понятние
Нет. Вопрос "Где последнее соостояние равно?" формулируется дословно как
(select max(...) keep ...) = 'Y'

Stax
3) если в tab1 много полей, удобнее
Про expression list не слышал?


что б о чем-то продолжать, надо увидеть решение без EXISTS

ps
Про expression list не слышал

....
stax
18 июн 19, 10:55    [21910384]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Stax
что б о чем-то продолжать, надо увидеть решение без EXISTS
JOIN же на предагрегированный по ID inline-view на базе tab2
18 июн 19, 11:03    [21910399]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Stax,

ну или куда Elic клонит - агрегитрованный скалярный коррелированный подзапрос с first/last во внутрях и с проверкой во вне на равенство Y
18 июн 19, 11:04    [21910403]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Dshedoo
Member

Откуда:
Сообщений: 393
Stax,

Вообще, учитывая то, что автор пытался в экзист, нам нужно посмотреть последнюю строку из tab2, и если она condition = 'Y', тогда вернуть из tab1.
Похоже, что автор не совсем в курсе как формируется rownum.

Скорее всего нужен либо мой первый вариант, либо что-нибудь типа:

  with tab1 as (
   select 1 id,'Anna' n from dual union all
   select 2,'Jon' n from dual union all
   select 3,'Jack' n from dual   
   )
   ,tab2 as (
   select 1 id,sysdate dat,'Y' condition from dual union all
   select 2 id,sysdate-1 dat,'Y' condition from dual union all
   select 2 id,sysdate-2 dat,'Y' condition from dual union all
   select 2 id,sysdate-3 dat,'N' condition from dual union all
   select 2 id,sysdate-4 dat,'Y' condition from dual union all         
   select 3 id,sysdate-4 dat,'N' condition from dual union all            
   select 2 id,sysdate-5 dat,'N' condition from dual union all
   select 2 id,sysdate-6 dat,'Y' condition from dual union all
   select 2 id,sysdate dat,'N' condition from dual )
select * from tab1 t
where (t.id,1) in (select id, row_number() OVER (partition by id order by dat desc) as rn from tab2 where condition = 'Y')
18 июн 19, 11:05    [21910406]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Dshedoo
Member

Откуда:
Сообщений: 393
Dshedoo,

*
where (t.id,1,'Y') in (select id, row_number() OVER (partition by id order by dat desc) as rn, condition from tab2)
18 июн 19, 11:07    [21910408]     Ответить | Цитировать Сообщить модератору
 Re: exists и order by  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Щукина Анна
Stax
что б о чем-то продолжать, надо увидеть решение без EXISTS
JOIN же на предагрегированный по ID inline-view на базе tab2

я ето понимаю (и могу срисовать)

где селект чтоб сравнить с exists?

.....
stax
18 июн 19, 11:08    [21910412]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить