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

Откуда: Владивосток
Сообщений: 1072
Добрый день всем!

Такая ситуация... есть две таблицы:

CREATE TABLE t1 (scn$ NUMBER, id NUMBER, value1 VARCHAR2(10));
CREATE TABLE t2 (scn$ NUMBER, id NUMBER, value1 VARCHAR2(10));

В этих двух таблицах записаны данные об одной сущности, скажем, все это клиенты. Просто часть информации в одной таблице и часть в другой. Реальная структура, конечно, отличается, но для того вопроса, помощи в котором хочу здесь попросить, трех этих колонок достаточно.

В t1.scn$ и t2.scn$ - system change numbers соответствующих модификаций. Например, запись информации об одном клиенте выливается в необходимость записи в две отдельные таблицы, код ID будет одинаковый, но у записи из одной таблицы scn будет, скажем, N, а у записи из другой таблицы будет M > N (или < N, смотря какая была очередность вставки).

Тестовый набор данных и затем вопрос:


T1.SCN$│ T1.ID │ T1.VALUE1
-------+---------+----------
1 │ 1 │ x1
2 │ 1 │ y1
5 │ 2 │ z1


T2.SCN$│ T2.ID │ T2.VALUE1
-------+---------+----------
3 │ 1 │ x2
4 │ 2 │ y2
6 │ 1 │ z2


Нужно получить такую таблицу:
T1.SCN$│T2.SCN$│ T1.ID │ T2.ID │ T1.VALUE1│ T2.VALUE1
-------+-------+---------+---------+----------+----------
1 │ │ 1 │ │ x1 │
2 │ 3 │ 1 │ 1 │ y1 │ x2
5 │ 4 │ 2 │ 2 │ z1 │ y2
│ 6 │ │ 1 │ │ z2


Т.е. нужно составить пары по связи через код клиента и с учетом порядка выполненного действия.
Например, сначала был UPDATE на первую таблицу (будет запись в T1), потом был снова UPDATE на первую таблицу (снова запись в T1) и после этого UPDATE на вторую (запись в T2). Первый UPDATE в результирующей сводной таблице должен быть без пары, т.к. "соответствущее" изменение второй таблицы произошло после второго update, а не до него.
19 сен 07, 04:55    [4685941]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
-- Начало эмуляции тестовых данных:
with
  t1 as
    (
      select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
      select 2 as scn$, 1 as id, 'y1' as value1 from dual union all
      select 5 as scn$, 2 as id, 'z1' as value1 from dual 
    ),
  t2 as
    (
      select 3 as scn$, 1 as id, 'x2' as value1 from dual union all
      select 4 as scn$, 2 as id, 'y2' as value1 from dual union all
      select 6 as scn$, 1 as id, 'z2' as value1 from dual
    )
-- Конец эмуляции тестовых данных:
--
-- Непосредственно сам запрос:
select max(decode(tbl_id,1,scn$)) as "T1.SCN$",
       max(decode(tbl_id,2,scn$)) as "T2.SCN$",
       max(decode(tbl_id,1,id)) as "T1.ID",
       max(decode(tbl_id,2,id)) as "T2.ID",
       max(decode(tbl_id,1,value1)) as "T1.VALUE1",
       max(decode(tbl_id,2,value1)) as "T2.VALUE1"
  from (
         select v1.scn$, v1.id, v1.value1, v1.tbl_id,
                sum(flag) over(partition by id order by scn$) grp_id
           from (
                  select v.scn$, v.id, v.value1, v.tbl_id,
                         decode(lag(tbl_id,1,tbl_id) 
                                over(partition by id 
                                         order by scn$),
                                tbl_id,1,
                                0) as flag
                    from (
                           select t1.*, 1 as tbl_id from t1 
                           union all
                           select t2.*, 2 as tbl_id from t2
                         ) v
                ) v1
       )
 group by id, grp_id
 order by 1 nulls last, 2 nulls first

Query finished, retrieving results...

T1.SCN$   T2.SCN$   T1.ID   T2.ID   T1.VALUE1   T2.VALUE1
-------   -------   -----   -----   ---------   ---------
      1                 1           x1
      2         3       1       1   y1          x2         
      5         4       2       2   z1          y2         
                6               1               z2

4 row(s) retrieved
19 сен 07, 06:38    [4685971]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Мм... за идею спасибо!
Но в общем случае выборка немножко неправильно работает. Попробую переработать... например, для такого варианта:


T1.SCN$│ T1.ID │ T1.VALUE1
-------+---------+----------
1 │ 1 │ x1
3 │ 2 │ y1
4 │ 1 │ z1


T2.SCN$│ T2.ID │ T2.VALUE1
-------+---------+----------
2 │ 1 │ x2


объединенная таблица должна быть такова:

T1.SCN$│T2.SCN$│ T1.ID │ T2.ID │ T1.VALUE1│ T2.VALUE1
-------+-------+---------+---------+----------+----------
1 │ 2 │ 1 │ 1 │ x1 │ x2
3 │ │ 2 │ │ y1 │
4 │ │ 1 │ │ z1 │

Сначала изменили запись в первой таблице, потом во второй. Это все считаем одним клиентом (т.е. записи в одной группе). Следующим действием в хронологическом порядке идет операция с клиентом с другим кодом, так что однозначно в другую группу, причем, без пары. И последнее действие снова не должно иметь пары, потому что в t2 нет соответствия по порядку scn$. А Ваш запрос вернул две группы: t1.scn$ = 3 (остальное пусто, это правильно) и t1.scn$ = 4, t2.scn$ = 2. Т.е. просто объединились записи по признаку "из одной таблицы". Объединяться как раз должны записи из разных таблиц.
19 сен 07, 07:10    [4686006]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
Вадиман
Но в общем случае выборка немножко неправильно работает.
вот тебе немного исправленный вариант:

-- Начало эмуляции тестовых данных:
with
  t1 as
    (
      select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
      select 3 as scn$, 2 as id, 'y1' as value1 from dual union all
      select 4 as scn$, 1 as id, 'z1' as value1 from dual 
    ),
  t2 as
    (
      select 2 as scn$, 1 as id, 'x2' as value1 from dual
    )
-- Конец эмуляции тестовых данных:
--
-- Непосредственно сам запрос:
select max(decode(tbl_id,1,scn$)) as "T1.SCN$",
       max(decode(tbl_id,2,scn$)) as "T2.SCN$",
       max(decode(tbl_id,1,id)) as "T1.ID",
       max(decode(tbl_id,2,id)) as "T2.ID",
       max(decode(tbl_id,1,value1)) as "T1.VALUE1",
       max(decode(tbl_id,2,value1)) as "T2.VALUE1"
  from (
         select v3.scn$, v3.id, v3.value1, v3.tbl_id,
                sum(flag) over(partition by id order by scn$) grp_id
           from (
                  select v2.scn$, v2.id, v2.value1, v2.tbl_id,
                         decode(lag(tbl_id,1,tbl_id) 
                                over(partition by id, xyz 
                                         order by scn$),
                                tbl_id,1,
                                0) as flag
                    from (
                           select v1.*, sum(flag) over(order by scn$) xyz
                           from (
                                  select v0.*,
                                         decode(lag(id) 
                                                over(order by scn$),
                                                id,0,
                                                1) as flag
                                    from (
                                           select t1.*, 1 as tbl_id from t1 
                                           union all
                                           select t2.*, 2 as tbl_id from t2
                                         ) v0
                               ) v1
                         ) v2
                ) v3
       ) v4
 group by id, grp_id
 order by 1 nulls last, 2 nulls first

Query finished, retrieving results...

T1.SCN$   T2.SCN$   T1.ID   T2.ID   T1.VALUE1   T2.VALUE1
-------   -------   -----   -----   ---------   ---------
      1         2       1       1   x1          x2
      3                 2           y1
      4                 1           z1

3 row(s) retrieved


Навсякий случай демонстрация работы нового варианта на старых тестовых данных:
-- Начало эмуляции тестовых данных:
with
  t1 as
    (
      select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
      select 2 as scn$, 1 as id, 'y1' as value1 from dual union all
      select 5 as scn$, 2 as id, 'z1' as value1 from dual 
    ),
  t2 as
    (
      select 3 as scn$, 1 as id, 'x2' as value1 from dual union all
      select 4 as scn$, 2 as id, 'y2' as value1 from dual union all
      select 6 as scn$, 1 as id, 'z2' as value1 from dual
    )
-- Конец эмуляции тестовых данных:
--
-- Непосредственно сам запрос:
select max(decode(tbl_id,1,scn$)) as "T1.SCN$",
       max(decode(tbl_id,2,scn$)) as "T2.SCN$",
       max(decode(tbl_id,1,id)) as "T1.ID",
       max(decode(tbl_id,2,id)) as "T2.ID",
       max(decode(tbl_id,1,value1)) as "T1.VALUE1",
       max(decode(tbl_id,2,value1)) as "T2.VALUE1"
  from (
         select v3.scn$, v3.id, v3.value1, v3.tbl_id,
                sum(flag) over(partition by id order by scn$) grp_id
           from (
                  select v2.scn$, v2.id, v2.value1, v2.tbl_id,
                         decode(lag(tbl_id,1,tbl_id) 
                                over(partition by id, xyz 
                                         order by scn$),
                                tbl_id,1,
                                0) as flag
                    from (
                           select v1.*, sum(flag) over(order by scn$) xyz
                           from (
                                  select v0.*,
                                         decode(lag(id) 
                                                over(order by scn$),
                                                id,0,
                                                1) as flag
                                    from (
                                           select t1.*, 1 as tbl_id from t1 
                                           union all
                                           select t2.*, 2 as tbl_id from t2
                                         ) v0
                               ) v1
                         ) v2
                ) v3
       ) v4
 group by id, grp_id
 order by 1 nulls last, 2 nulls first

Query finished, retrieving results...

T1.SCN$   T2.SCN$   T1.ID   T2.ID   T1.VALUE1   T2.VALUE1
-------   -------   -----   -----   ---------   ---------
      1                 1           x1
      2         3       1       1   y1          x2         
      5         4       2       2   z1          y2         
                6               1               z2

4 row(s) retrieved
Потестируй его...
19 сен 07, 08:45    [4686103]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Снова нюанс :(
Если попробовать затолкать в T1.SCN$ значения (1, 2, 4, 5), в T2.SCN$ (3), и везде одинаковый ID, то ваша последняя выборка модификацию с SCN$ = 2 совсем выкинет.
19 сен 07, 09:04    [4686147]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
Вадиман
Снова нюанс :(
Если попробовать затолкать в T1.SCN$ значения (1, 2, 4, 5), в T2.SCN$ (3), и везде одинаковый ID, то ваша последняя выборка модификацию с SCN$ = 2 совсем выкинет.
Как определить, по какому принципу разбить записи на пары?
При таком раскладе можно выделить пары как минимум двумя способами:
1) пара - scn$(2,3), scn$(4) - лишний
2) scn$(2) - лишний, пара - scn$(3,4)
19 сен 07, 09:23    [4686211]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
при таком раскладе никак, но сойдет любой вариант, (4, 3) тоже будет нормально.
19 сен 07, 09:26    [4686232]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
Вадиман
при таком раскладе никак, но сойдет любой вариант, (4, 3) тоже будет нормально.
Примерно так?
with
  t1 as
    (
      select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
      select 2 as scn$, 1 as id, 'y1' as value1 from dual union all
      select 4 as scn$, 1 as id, 'z1' as value1 from dual union all
      select 5 as scn$, 1 as id, 'w1' as value1 from dual 
    ),
  t2 as
    (
      select 3 as scn$, 1 as id, 'x2' as value1 from dual
    )
-- 
--
-- 
select max(decode(tbl_id,1,scn$)) as "T1.SCN$",
       max(decode(tbl_id,2,scn$)) as "T2.SCN$",
       max(decode(tbl_id,1,id)) as "T1.ID",
       max(decode(tbl_id,2,id)) as "T2.ID",
       max(decode(tbl_id,1,value1)) as "T1.VALUE1",
       max(decode(tbl_id,2,value1)) as "T2.VALUE1"
  from (
         select v4.*,
                trunc((row_number() over(partition by id, grp_id order by scn$) - 1) / 2) + 1 sub_grp_id
           from (
                  select v3.scn$, v3.id, v3.value1, v3.tbl_id,
                         sum(flag) over(partition by id order by scn$) grp_id
                    from (
                           select v2.scn$, v2.id, v2.value1, v2.tbl_id,
                                  decode(lag(tbl_id,1,tbl_id) 
                                         over(partition by id, xyz 
                                                  order by scn$),
                                         tbl_id,1,
                                         0) as flag
                             from (
                                    select v1.*, sum(flag) over(order by scn$) xyz
                                    from (
                                           select v0.*,
                                                  decode(lag(id) 
                                                         over(order by scn$),
                                                         id,0,
                                                         1) as flag
                                             from (
                                                    select t1.*, 1 as tbl_id from t1 
                                                    union all
                                                    select t2.*, 2 as tbl_id from t2
                                                  ) v0
                                        ) v1
                                  ) v2
                         ) v3
                ) v4
       ) v5
 group by id, grp_id, sub_grp_id
 order by 1 nulls last, 2 nulls first

Query finished, retrieving results...

T1.SCN$   T2.SCN$   T1.ID   T2.ID   T1.VALUE1   T2.VALUE1
-------   -------   -----   -----   ---------   ---------
      1                 1           x1
      2         3       1       1   y1          x2
      4                 1           z1
      5                 1           w1

4 row(s) retrieved
19 сен 07, 09:46    [4686342]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Не примерно, а ИМЕННО ТАК!
Спасибо огромное. Уже два дня камнем висело на душе. В самом начале не в ту сторону завернул в своих исследованиях, надо было отойти от компа где-то на полдня :)
19 сен 07, 10:04    [4686425]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Elic
Member

Откуда:
Сообщений: 29979
with
  t1 as
    (
      select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
      select 2 as scn$, 1 as id, 'y1' as value1 from dual union all
      select 4 as scn$, 1 as id, 'z1' as value1 from dual union all
      select 5 as scn$, 1 as id, 'a1' as value1 from dual union all
      select 6 as scn$, 1 as id, 'b1' as value1 from dual union all
      select 8 as scn$, 1 as id, 'c1' as value1 from dual union all
      select 10 as scn$, 1 as id, 'd1' as value1 from dual
    ),
  t2 as
    (
      select 3 as scn$, 1 as id, 'x2' as value1 from dual union all
      select 7 as scn$, 1 as id, 'y2' as value1 from dual union all
      select 9 as scn$, 1 as id, 'z2' as value1 from dual union all
      select 11 as scn$, 1 as id, 'a2' as value1 from dual
    )
--
select
    max(decode(tbl_id,1,scn$))   as "T1.SCN$",
    max(decode(tbl_id,2,scn$))   as "T2.SCN$",
    max(decode(tbl_id,1,id))     as "T1.ID",
    max(decode(tbl_id,2,id))     as "T2.ID",
    max(decode(tbl_id,1,value1)) as "T1.VALUE1",
    max(decode(tbl_id,2,value1)) as "T2.VALUE1"
  from
  ( select scn$, id, value1, tbl_id, grp_id,
         decode(flag, 0, trunc(sum(flag2) over(partition by id, grp_id order by scn$)/2), 0) grp2_id
      from
      ( select scn$, id, value1, tbl_id, flag,
            sum(flag) over(partition by id order by scn$) grp_id,
            decode(flag, lag(flag, 1, flag) over (partition by id order by scn$), 1, 0) as flag2
          from
          ( select scn$, id, value1, tbl_id,
                decode(lag(tbl_id, 1, tbl_id) over(partition by id order by scn$), tbl_id, 1, 0) as flag
              from
              ( select t1.*, 1 as tbl_id from t1
                union all
                select t2.*, 2 as tbl_id from t2
              )
          )
      )
  )
 group by id, grp_id, grp2_id
 order by id, grp_id, grp2_id
;

      T1.SCN$       T2.SCN$         T1.ID         T2.ID T1 T2
------------- ------------- ------------- ------------- -- --
            1                           1               x1
            2             3             1             1 y1 x2
            4                           1               z1
            5                           1               a1
            6             7             1             1 b1 y2
            8             9             1             1 c1 z2
           10            11             1             1 d1 a2

7 rows selected.
19 сен 07, 10:15    [4686503]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Elic
Member

Откуда:
Сообщений: 29979
Бабичев Сергей
trunc((x - 1) / 2) + 1
ceil(x/2)

Кстати, у меня на два from-а меньше. Просто правильно применённый start_of_group дважды :)
19 сен 07, 10:29    [4686605]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Volder
Member

Откуда: Москва
Сообщений: 474
Elic
Кстати, у меня на два from-а меньше.
моделькой можно еще два from'a отчипнуть :)

SQL> with
  2    t1 as
  3      (
  4        select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
  5        select 2 as scn$, 1 as id, 'y1' as value1 from dual union all
  6        select 4 as scn$, 1 as id, 'z1' as value1 from dual union all
  7        select 5 as scn$, 1 as id, 'a1' as value1 from dual union all
  8        select 6 as scn$, 1 as id, 'b1' as value1 from dual union all
  9        select 8 as scn$, 1 as id, 'c1' as value1 from dual union all
 10        select 10 as scn$, 1 as id, 'd1' as value1 from dual
 11      ),
 12    t2 as
 13      (
 14        select 3 as scn$, 1 as id, 'x2' as value1 from dual union all
 15        select 7 as scn$, 1 as id, 'y2' as value1 from dual union all
 16        select 9 as scn$, 1 as id, 'z2' as value1 from dual union all
 17        select 11 as scn$, 1 as id, 'a2' as value1 from dual
 18      ),
 19    t3 as (select t1.*, 'A' as tbl_id from t1
 20            union all
 21           select t2.*, 'B' as tbl_id from t2)
 22     --
 23  select
 24      max(decode(tbl_id,'A',scn$))   as "T1.SCN$",
 25      max(decode(tbl_id,'B',scn$))   as "T2.SCN$",
 26      max(decode(tbl_id,'A',id))     as "T1.ID",
 27      max(decode(tbl_id,'B',id))     as "T2.ID",
 28      max(decode(tbl_id,'A',value1)) as "T1.VALUE1",
 29      max(decode(tbl_id,'B',value1)) as "T2.VALUE1"
 30  from (select * from t3
 31         model
 32          partition by (id)
 33          dimension by (row_number () over (partition by id order by scn$) rn)
 34          measures (scn$, value1, tbl_id, 0 gr)
 35           rules(gr[any] order by rn = nvl(case when tbl_id[CV()-1]=tbl_id[CV()] or gr[CV()-1]=gr[CV()-2]
 36                                                then gr[CV()-1]+1
 37                                                else gr[CV()-1]
 38                                            end, 0)
 39                )
 40        )
 41        group by id, gr
 42        order by id, gr
 43  /

   T1.SCN$    T2.SCN$      T1.ID      T2.ID T1.VALUE1 T2.VALUE1
---------- ---------- ---------- ---------- --------- ---------
         1                     1            x1        
         2          3          1          1 y1        x2
         4                     1            z1        
         5                     1            a1        
         6          7          1          1 b1        y2
         8          9          1          1 c1        z2
        10         11          1          1 d1        a2

7 rows selected 
19 сен 07, 17:37    [4690124]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
очень интересное решение через модель... сам вначале пытался, но не мог придумать адресацию :)
классно
20 сен 07, 01:28    [4691400]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Кстати, Elic и Volder, предлагаю посмотреть вам на результаты ваших вариантов запроса по следующим данным (value1 оставил в стороне, это всего лишь атрибут и не в нем суть):


T1.SCN$│ T1.ID
-------+---------
1 │ 1
3 │ 2
5 │ 1
6 │ 3
7 │ 1


T2.SCN$│ T2.ID
-------+---------
2 │ 1
4 │ 2
8 │ 3


сводная таблица должна быть такая:

T1.SCN$│T2.SCN$│ T1.ID │ T2.ID
-------+-------+---------+---------
1 │ 2 │ 1 │ 1
3 │ 4 │ 2 │ 2
5 │ │ 1 │
6 │ │ 3 │
7 │ │ 1 │
│ 8 │ │ 3

Именно такая выводится у Сергея Бабичева. А в ваших вариантах объединились строки scn$ (6, 8), хотя между ними есть модификация с scn$=7 из другого источника, так что явно обе таблицы в случае scn$=(6, 8) не обновлялись последовательно.
20 сен 07, 01:54    [4691417]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
Elic
Бабичев Сергей
trunc((x - 1) / 2) + 1
ceil(x/2)
У-гу, каюсь - постоянно забываю о существовании в Oracle SQL таких функций, как ceil и floor
20 сен 07, 06:08    [4691488]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Elic
Member

Откуда:
Сообщений: 29979
Вадиман
Именно такая выводится у Сергея Бабичева. А в ваших вариантах объединились строки scn$ (6, 8), хотя между ними есть модификация с scn$=7 из другого источника, так что явно обе таблицы в случае scn$=(6, 8) не обновлялись последовательно.
Это всё из-за (не)умения задавать вопросы :) Каждый понял по-своему.
Но это не проблема для головы
with
  t1 as
    (
      select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
      select 3 as scn$, 2 as id, 'y1' as value1 from dual union all
      select 5 as scn$, 1 as id, 'z1' as value1 from dual union all
      select 6 as scn$, 3 as id, 'a1' as value1 from dual union all
      select 7 as scn$, 1 as id, 'b1' as value1 from dual
    ),
  t2 as
    (
      select 2 as scn$, 1 as id, 'x2' as value1 from dual union all
      select 4 as scn$, 2 as id, 'y2' as value1 from dual union all
      select 8 as scn$, 3 as id, 'z2' as value1 from dual
    )
--
select
    max(decode(tbl_id,1,scn$))   as "T1.SCN$",
    max(decode(tbl_id,2,scn$))   as "T2.SCN$",
    max(decode(tbl_id,1,id))     as "T1.ID",
    max(decode(tbl_id,2,id))     as "T2.ID",
    max(decode(tbl_id,1,value1)) as "T1.VALUE1",
    max(decode(tbl_id,2,value1)) as "T2.VALUE1"
  from
  ( select scn$, id, value1, tbl_id, grp_id,
         decode(flag, 0, trunc(sum(flag2) over (partition by grp_id order by scn$)/2), 0) grp2_id
      from
      ( select scn$, id, value1, tbl_id, flag,
            sum(flag) over (order by scn$) grp_id,
            decode(flag, lag(flag, 1, flag) over (order by scn$), 1, 0) as flag2
          from
          ( select scn$, id, value1, tbl_id,
                case when lag(id, 1, -id) over (order by scn$) <> id
                          or
                          lag(tbl_id, 1, tbl_id) over (order by scn$) = tbl_id
                     then 1
                     else 0
                end as flag
              from
              ( select t1.*, 1 as tbl_id from t1
                union all
                select t2.*, 2 as tbl_id from t2
              )
          )
      )
  )
 group by id, grp_id, grp2_id
 order by grp_id, grp2_id
;

      T1.SCN$       T2.SCN$         T1.ID         T2.ID T1 T2
------------- ------------- ------------- ------------- -- --
            1             2             1             1 x1 x2
            3             4             2             2 y1 y2
            5                           1               z1
            6                           3               a1
            7                           1               b1
                          8                           3    z2

6 rows selected.

Вадиман
по следующим данным (value1 оставил в стороне, это всего лишь атрибут и не в нем суть):
На будущее. Не приводи своих грёбанных таблиц. Приводи скрипты с данными (как у нас). Этим ты повысишь свои шансы. Когда-нибудь нам надоест тайпить за тебя
20 сен 07, 09:17    [4691733]     Ответить | Цитировать Сообщить модератору
 Re: нужен совет по выборке  [new]
Volder
Member

Откуда: Москва
Сообщений: 474
Вадиман
...value1 оставил в стороне, это всего лишь атрибут и не в нем суть...
это надо делать при постановке задачи :)

если нумерация сквозная, можно:

SQL> with
  2    t1 as
  3      (
  4        select 1 as scn$, 1 as id, 'x1' as value1 from dual union all
  5        select 3 as scn$, 2 as id, 'y1' as value1 from dual union all
  6        select 5 as scn$, 1 as id, 'z1' as value1 from dual union all
  7        select 6 as scn$, 3 as id, 'a1' as value1 from dual union all
  8        select 7 as scn$, 1 as id, 'b1' as value1 from dual
  9      ),
 10    t2 as
 11      (
 12        select 2 as scn$, 1 as id, 'x2' as value1 from dual union all
 13        select 4 as scn$, 2 as id, 'y2' as value1 from dual union all
 14        select 8 as scn$, 3 as id, 'z2' as value1 from dual
 15      ),
 16      t3 as (select t1.*, 'A' as tbl_id from t1
 17              union all
 18             select t2.*, 'B' as tbl_id from t2)
 19       --
 20    select
 21        max(decode(tbl_id,'A',scn$))   as "T1.SCN$",
 22        max(decode(tbl_id,'B',scn$))   as "T2.SCN$",
 23        max(decode(tbl_id,'A',id))     as "T1.ID",
 24        max(decode(tbl_id,'B',id))     as "T2.ID",
 25        max(decode(tbl_id,'A',value1)) as "T1.VALUE1",
 26        max(decode(tbl_id,'B',value1)) as "T2.VALUE1"
 27    from (select * from t3
 28           model
 29            dimension by (row_number () over (order by scn$) rn)
 30            measures (id, scn$, value1, tbl_id, 0 gr)
 31             rules(gr[any] order by rn = nvl(case when tbl_id[CV()-1]=tbl_id[CV()] or gr[CV()-1]=gr[CV()-2] or id[CV()-1]<>id[CV()]
 32                                                  then gr[CV()-1]+1
 33                                                  else gr[CV()-1]
 34                                              end, 0)
 35                  )
 36          )
 37          group by gr
 38          order by coalesce("T1.SCN$","T2.SCN$")
 39  /

   T1.SCN$    T2.SCN$      T1.ID      T2.ID T1.VALUE1 T2.VALUE1
---------- ---------- ---------- ---------- --------- ---------
         1          2          1          1 x1        x2
         3          4          2          2 y1        y2
         5                     1            z1        
         6                     3            a1        
         7                     1            b1        
                    8                     3           z2

6 rows selected

SQL> 
20 сен 07, 11:36    [4692608]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить