create table T( id number primary key,
Name varcar2(32))

надо используя только T
получить t.ID,N

Где N имеет одно значение для одинаковых T.NAME <> NULL
и если T.NAME = null, то N должно быть уникально и <> N для которых Т.NAME <> NULL

Пример

ID NAME
1 abc
2 abc
3 abcd
4 abcd
5
6

результат

ID N
1 1
2 1
3 2
4 2
5 3
6 4

Здравствуйте, Аноним, Вы писали:

select id, decode(t.name, null, -rownum, names.n) n
  from t, (select rownum n, name 
             from (select distinct name from t where name is not null)
          ) names
 where t.name = names.name(+)

Но это весьма не эффективно.

Задача весьма странная. Можно узнать, зачем вам это? Такое впечатление, что вы сами себя запутываете.

Здравствуйте, wildwind, Вы писали:



Но это весьма не эффективно.

а так не лучше

select ID,decode(Name,null,
Dbms_Utility.get_hash_value(RowID,0,power(2,30)),
Dbms_Utility.get_hash_value(NAME,0,power(2,30)))
from T

Здравствуйте, Аноним, Вы писали:

А>результат

А>ID N
А>1 1
А>2 1
А>3 2
А>4 2
А>5 3
А>6 4

Предложу ещё вариант:

select NAME,rownum
from (
select nvl(T.NAME, T.rowid), max(T.NAME) NAME
from MY_TABLE T
group by nvl(T.NAME, T.rowid)
)

Здравствуйте, Аноним, Вы писали:

А>а так не лучше

По эффективности видимо намного лучше. Но остается возможность коллизии.

Здравствуйте, Аноним, Вы писали:

А>Предложу ещё вариант:

А>select NAME,rownum
А>from (
А> select nvl(T.NAME, T.rowid), max(T.NAME) NAME
А> from MY_TABLE T
А> group by nvl(T.NAME, T.rowid)
А> )

Результат не тот, что требуется.

Здравствуйте, wildwind, Вы писали:


По эффективности видимо намного лучше. Но остается возможность коллизии.

Какой коллизии? (да, заранее известо,что соличество выбранных строк < power(2,30)

Здравствуйте, Аноним, Вы писали:

А>надо используя только T
А>получить t.ID,N
А>Где N имеет одно значение для одинаковых T.NAME <> NULL
А>и если T.NAME = null, то N должно быть уникально и <> N для которых Т.NAME <> NULL

Вот что удалось придумать.

create table T(
id number primary key,
Name varchar2(32)
);

insert into t values(1,'abc');
insert into t values(2,'abc');
insert into t values(3,'abcd');
insert into t values(4,'abcd');
insert into t values(5, null);
insert into t values(6, null);
commit;

select t.id, nc.n
from
t,
(
select rownum n, name, id
from
(select distinct name, decode(name, null, id) id from t)
) nc
where
t.name = nc.name or t.id = nc.id;

ID N
--- -----
5 3
6 4
1 1
2 1
3 2
4 2

6 rows selected.

1. Все непустые name имеют уникальные номера.
2. Каждая строка с пустым name имеет уникальное значение в поле n.
3. Остальные строки имеют в поле n номер соответсвующий значению name определённый в п. 1.

Задача красиво решается в Oracle9i.

select
id,
dense_rank() over (order by name, nvl2(name, null, id) nulls last) n
from t;

Результат:
ID N
--- -----
1 1
2 1
3 2
4 2
5 3
6 4

6 rows selected.

drop table T;

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, Аноним, Вы писали:

А>>Предложу ещё вариант:

А>>select NAME,rownum
А>>from (
А>> select nvl(T.NAME, T.rowid), max(T.NAME) NAME
А>> from MY_TABLE T
А>> group by nvl(T.NAME, T.rowid)
А>> )

W>Результат не тот, что требуется.

Интересно почему же ? Напротив, Ваш запрос вообще отрицательные индексы присваивает, чего в исходной задаче не было.

Здравствуйте, Аноним, Вы писали:

"Дикий" запрос, но сразу в голову пришел как первый попавшийся

SELECT T1.ID, T3.RN
FROM T1,
(SELECT T2.NAME, ROWNUM RN
FROM
(SELECT DISTINCT t1.name
FROM t1
WHERE t1.NAME IS NOT NULL) T2) T3
WHERE T1.NAME = T3.NAME
UNION ALL
SELECT T6.ID, T5.MRN + T6.RN
FROM
(SELECT MAX (RN) MRN FROM
(SELECT T4.NAME, ROWNUM RN
FROM
(SELECT DISTINCT t1.name
FROM t1
WHERE t1.NAME IS NOT NULL) T4)) T5,
(SELECT T1.ID, T1.NAME, ROWNUM RN
FROM T1
WHERE T1.NAME IS NULL) T6

Экскюзэ муа. В обозначениях задачи, вместо "t1" — "t". Вот так.

SELECT T.ID, T3.RN
  FROM T,
    (SELECT T2.NAME, ROWNUM RN
       FROM 
         (SELECT DISTINCT t.name 
            FROM  t
            WHERE t.NAME IS NOT NULL
         ) T2
    ) T3
  WHERE T.NAME = T3.NAME    
UNION ALL
SELECT T6.ID, T5.MRN + T6.RN
FROM
  (SELECT MAX (RN) MRN FROM
     (SELECT T4.NAME, ROWNUM RN
        FROM 
         (SELECT DISTINCT t.name 
            FROM t
            WHERE t.NAME IS NOT NULL
         ) T4
     )
  ) T5,
  (SELECT T.ID, T.NAME, ROWNUM RN
     FROM T
     WHERE T.NAME IS NULL
  ) T6

Здравствуйте, Аноним, Вы писали:

А>По эффективности видимо намного лучше. Но остается возможность коллизии.
А>Какой коллизии? (да, заранее известо,что соличество выбранных строк < power(2,30)
Это хорошо, что меньше, в исходной задаче этого не было. Но это еще не гарантирует от коллизий.

Здравствуйте, Аноним, Вы писали:

W>>Результат не тот, что требуется.

А>Интересно почему же ? Напротив, Ваш запрос вообще отрицательные индексы присваивает, чего в исходной задаче не было.

Требуется "получить t.ID,N", а у вас NAME. А об (не)отрицательности N действительно ничего не было.

Здравствуйте, mcureenab, Вы писали:

M>Задача красиво решается в Oracle9i.

M>select
M> id,
M> dense_rank() over (order by name, nvl2(name, null, id) nulls last) n
M>from t;

Да! Однако dense_rank есть и в 8.1.6. Так что это можно считать лучшим решением. А также, учитывая правила сортировки по умолчанию, можно записать короче :

select id, dense_rank() over (order by name, nvl(name, id)) n from t

Всем спасибо