Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Частичное исключение null-евых строк  [new]
Евгений Семионов
Member

Откуда:
Сообщений: 12
Подскажите, как можно реализовать такую вещь - есть вот такой набор данных
select * from
(
select 1 as actnum, 1 as subactnum from dual
union all
select 1 as actnum, null as subactnum from dual
union all
select 2 as actnum, null as subactnum from dual
union all
select 3 as actnum, null as subactnum from dual
union all
select 3 as actnum, 2 as subactnum from dual
union all
select 3 as actnum, 3 as subactnum from dual
)

в результате из него нужно выбрать все строки, для которых выполняются условия: (subactnum не null) или (subactnum null и запись соответствующим ему actnum одна). то есть в результате должно получится следующее
actnum, subactnum
1 1
2 null
3 2
3 3

возможно все очень просто, но моих знаний пока не хватает. буду благодарен за любые советы.
24 окт 06, 05:26    [3299356]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1516
-- Ваши тестовые данные
with test_table 
  as (
        select 1 as actnum, 1 as subactnum from dual 
        union all
        select 1 as actnum, null as subactnum from dual 
        union all
        select 2 as actnum, null as subactnum from dual 
        union all
        select 3 as actnum, null as subactnum from dual 
        union all
        select 3 as actnum, 2 as subactnum from dual 
        union all
        select 3 as actnum, 3 as subactnum from dual 
     )
-- Один из вариантов решения поставленной задачи
select *
  from (
          select test_table.*, 
                 case 
                    when subactnum is not null 
                         or 
                         count(1) over(partition by actnum) = 1
                    then 1 
                    else 0
                 end flag
            from test_table 
       )
 where flag = 1

Query finished, retrieving results...

ACTNUM   SUBACTNUM   FLAG
------   ---------   ----
     1           1      1
     2                  1
     3           2      1
     3           3      1

4 row(s) retrieved
24 окт 06, 07:06    [3299399]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Dr. Simon
Member

Откуда:
Сообщений: 134
Как вариант:


select *
from test_tbl
where subactnum is not null
union all
select actnum, subactnum
from test_tbl q1
where subactnum is null
and not exists (select 1
from test_tbl sq1
where sq1.rowid <> q1.rowid
and sq1.actnum = q1.actnum)
24 окт 06, 09:22    [3299673]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Dr. Simon
Member

Откуда:
Сообщений: 134
Не, вот так лучше:

select actnum, subactnum
from test_tbl q1
where ((subactnum is null
and not exists (select 1
from test_tbl sq1
where sq1.rowid <> q1.rowid
and sq1.actnum = q1.actnum)
)
or
subactnum is not null
)

:)
24 окт 06, 09:26    [3299687]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 65908
Блог
Евгений Семионов

Обратите внимание - у Вас вышел замечательный пример к одному важному факту: SQL такой язык, что "четко сформулировать, что же именно нужно" - это почти то же самое, что и "решить задачу". Вы сформулировали, осталось лишь один в один перевести сказанное на SQL. Смотрите:

1. (subactnum не null) -- (subactnum is not null)

2. или (subactnum null -- or (subactnum is null

3. и запись соответствующим ему actnum одна -- and ( select count(*) ... ) = 1

Итого получаем

SQL> with
  2    data as ( select 1 as actnum, 1 as subactnum from dual
  3              union all
  4              select 1 as actnum, null as subactnum from dual
  5              union all
  6              select 2 as actnum, null as subactnum from dual
  7              union all
  8              select 3 as actnum, null as subactnum from dual
  9              union all
 10              select 3 as actnum, 2 as subactnum from dual
 11              union all
 12              select 3 as actnum, 3 as subactnum from dual )
 13  select
 14    *
 15  from
 16    data d1
 17  where
 18    d1.subactnum is not null or
 19    (( select count(*) from data d2 where d2.actnum = d1.actnum ) = 1 ) ;

    ACTNUM  SUBACTNUM
---------- ----------
         1          1
         2 
         3          2
         3          3

Дальше уже можно говорить об оптимизации, но главное - результат получен.
24 окт 06, 09:35    [3299736]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1516
Dr. Simon
Не, вот так лучше:
Что-то мне подсказывает, что запросы в таком виде вообще будут нежизнеспособными...
Особенно смущает упоминание ROWID в подзапросе EXISTS.
24 окт 06, 09:40    [3299773]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Dr. Simon
Member

Откуда:
Сообщений: 134
И тем не менее это работает:)
24 окт 06, 09:44    [3299799]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1516
Dr. Simon
И тем не менее это работает:)

with test_tbl
  as (
        select /*+ materialize*/ -- Без хинта ловим сообщение:
-- No connection to test@test
              * 
        from (
                select 1 as actnum, 1 as subactnum from dual 
                union all
                select 1 as actnum, null as subactnum from dual 
                union all
                select 2 as actnum, null as subactnum from dual 
                union all
                select 3 as actnum, null as subactnum from dual 
                union all
                select 3 as actnum, 2 as subactnum from dual 
                union all
                select 3 as actnum, 3 as subactnum from dual 
             )
     )
--
select actnum, subactnum
  from test_tbl q1
 where ((subactnum is null
        and not exists (select 1
           from test_tbl sq1
            where sq1.rowid <> q1.rowid
              and sq1.actnum = q1.actnum)
        )
         or 
         subactnum is not null
        )
        
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc

Проходит, если создать физическую таблицу TEST_TBL
24 окт 06, 09:55    [3299861]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Dr. Simon
Member

Откуда:
Сообщений: 134
Ну да... точно... я-то для физической таблицы и делаю

Жизнеспособность варианта зависит от наличия таблицы...
24 окт 06, 10:00    [3299891]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Dr. Simon
Member

Откуда:
Сообщений: 134
Продолжая тему моего варианта, можно сделать так:


with test_tbl
as (
select /*+ materialize*/
*
from (
select 1 as actnum, 1 as subactnum from dual
union all
select 1 as actnum, null as subactnum from dual
union all
select 2 as actnum, null as subactnum from dual
union all
select 3 as actnum, null as subactnum from dual
union all
select 3 as actnum, 2 as subactnum from dual
union all
select 3 as actnum, 3 as subactnum from dual
)
)
--
select actnum, subactnum from
(select actnum, subactnum, rownum rn from test_tbl) q1
where ((subactnum is null
and not exists (select 1
from (select actnum, subactnum, rownum rn from test_tbl) sq1
where sq1.rn <> q1.rn
and sq1.actnum = q1.actnum)
)
or
subactnum is not null
)
24 окт 06, 10:05    [3299922]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Elic
Member

Откуда:
Сообщений: 30178
Щукина Анна
                         count(1) over(partition by actnum) = 1
В целом я поддерживаю данный вариант. Только, ради бога, не надо тиражировать мифы
24 окт 06, 10:35    [3300117]     Ответить | Цитировать Сообщить модератору
 Re: Частичное исключение null-евых строк  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1516
Elic
Только, ради бога, не надо тиражировать мифы
Даже и в мыслях такого не было...

Просто с детства в голове засел счет именно в единицах, а не в звездочках :)
не более того...
24 окт 06, 11:17    [3300457]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить