Убедитесь, что значения во всех столбцах различны в sql - Oracle

Проверьте следующие примеры. Сначала я группирую значения в коллекцию. Я использовал встроенный SYS.DBMS_DEBUG_VC2COLL, но лучше, если вы создадите свой выиграл. Затем я беру эту коллекцию, а затем делаю MULTISET UNION DISTINCT с пустой коллекцией того же типа. Это удалит повторяющиеся записи.

select * from
 (select sys.dbms_debug_vc2coll(1,2,3) a, 
         sys.dbms_debug_vc2coll(1,2,3) multiset union distinct sys.dbms_debug_vc2coll() b
 from dual)
where a=b;

select * from
 (select sys.dbms_debug_vc2coll(1,2,3,1) a, 
         sys.dbms_debug_vc2coll(1,2,3,1) multiset union distinct sys.dbms_debug_vc2coll() b
 from dual)
where a=b

Наконец, я сравниваю эту коллекцию DISTINCT с оригинальной коллекцией. Если они совпадают, то в коллекции уже есть только уникальные значения.

Мне кажется, что эти 14 столбцов денормализованы и на самом деле должны быть подтаблицей с ограничением уникального индекса.

если это ограничение данных - тогда я бы поместил его в триггер - и сравнил каждое значение в PLSQL. (я думаю, что это рекомендуется на основе вашего описания)

если это запрос, то у вас будет довольно большое предложение where, проверяющее каждый столбец по сравнению с другими.

редактировать:

что-то типа:

select * from mytable where
( col1=col2 or col1=col3 or col1=col4 or col1=col5 ... )
or
( col2=col3 or col2=col4 or col2=col5 ... )
or
( col3=col4 or col3=col5 ... )
etc...

Вот еще один способ сделать это в SQL. Этот пример для трех столбцов. Вы просто добавляете больше предложений union all select ..., чтобы добавить больше столбцов в чек. Он вернет ROWID любой строки, для которой общее количество столбцов больше, чем количество различных значений.

Я предполагаю, что ни один из столбцов не может содержать NULL. Если это проблема, это может дать вам ложные срабатывания.

select rowid,count(*),count(distinct col)
from (
select rowid,col1 col from the_table
  union all
select rowid,col2 col from the_table
  union all
select rowid,col3 col from the_table
)
group by rowid
having count(distinct col) < count(*)

Очень грязный, неуклюжий метод.... но он должен работать с 9i

SELECT *
  FROM ( SELECT keyIdentifierField,
                COUNT(testField) AS fieldCount,
                COUNT(DISTINCT testField) AS distinctFieldCount
           FROM ( SELECT keyIdentifierField,
                         col1 AS testField
                    FROM myTable
                  UNION ALL
                  SELECT keyIdentifierField,
                         col2 AS testField
                    FROM myTable
                  UNION ALL
                  SELECT keyIdentifierField,
                         col3 AS testField
                    FROM myTable
                  ...
                  UNION ALL
                  SELECT keyIdentifierField,
                         col14 AS testField
                    FROM myTable
                )
          GROUP BY keyIdentifierField
       )
 WHERE fieldCount <> distinctFieldCount

Другой подход, который будет работать в 9i. Я не гарантирую производительность, только то, что это чисто SQL-подход, который работает на 9i, но выполнение 10 000 строк с 5 столбцами заняло доли секунды, так что это разумно.

create table test (
   uniqueKey number, 
   c1 number,
   c2 number,
   c3 number,
   c4 number,
   c5 number) 

Создайте несколько тестовых примеров — каждая 4-я строка плохая — мы используем псевдотаблицу целых чисел

    insert into test 
    select  r,1,2,3,4,CASE WHEN MOD(r,4)=0 THEN 4 ELSE 5 END
    FROM (SELECT rownum r from dual connect by rownum <= 10000);

Теперь преобразуйте значения столбцов обратно в строки, используя тот же метод псевдотаблицы для последовательности целых чисел.

SELECT uniqueKey,r from
(
    SELECT rownum r from dual connect by rownum <= 100 
/* 100 is the max value in any of our columns */
) numbers,
  test
WHERE r in (c1,c2,c3,c4,c5)

Это дает набор уникальных чисел для каждой строки.

Чтобы найти недопустимые строки, просто проверьте, меньше ли количество столбцов.

SELECT uniqueKey  from
(
    SELECT rownum r from dual connect by rownum <= 100 /* Our max potential value */
) numbers,
  test
WHERE r in (c1,c2,c3,c4,c5)
GROUP BY uniqueKey
HAVING COUNT(r) < 5

К сожалению, это не говорит вам, какие значения дублируются, но идентифицирует ваши проблемные строки.

Если у вас есть только ОДИН возможный повторяющийся номер, возможны решения с использованием битовых операторов. Если вы используете Oracle 11, вы можете UNPIVOT данные столбца в строки,

Предполагая, что ваша таблица имеет уникальный ключ с именем UniqueKey

SELECT  UniqueKey,cvalue
FROM
(SELECT *
FROM yourTable
   UNPIVOT INCLUDE NULLS (cvalue FOR check_values IN (col1, col2, col3, col4))
)
GROUP BY UniqueKey,cvalue HAVING count(cvalue) > 1

Это может быть синтаксически немного неправильно, так как у меня нет под рукой тестовой базы данных Ora 11.

До Oracle 11 единственным способом сделать что-то подобное было использование типов объектов и pl/sql (функция, которая получает идентификатор, возвращает список значений столбца в этой строке в виде коллекции, а затем присоединяется к этой функции к идентификатору).

Вероятно, проще просто сделать это в pl/sql (создать массив, проиндексированный Integer, для каждого столбца проверить .exists(value) перед установкой - если он существует, у вас есть дубликат). Вызывайте функцию один раз для каждого столбца в строке и сбрасывайте массив между строками.

В качестве альтернативы - если это разовая проверка целостности - создайте временную таблицу для пар ключ/значение, уникальный ключ для пары, затем вставьте каждое значение в каждую строку против ключа и позвольте Oracle выбросить ваши ошибки.