Каков SQL-запрос, чтобы перечислить все строки, которые имеют 2 подстроки столбцов, как дубликаты?

Question

У меня есть таблица с избыточными данными, и я пытаюсь определить все строки, которые имеют повторяющиеся подстроки (из-за отсутствия лучшего слова). Под строкой я имею в виду, учитывая COL1 а также COL2 только.

Допустим, у меня есть что-то вроде этого:

 COL1   COL2   COL3
 ---------------------
 aa     111    blah_x
 aa     111    blah_j
 aa     112    blah_m
 ab     111    blah_s
 bb     112    blah_d
 bb     112    blah_d
 cc     112    blah_w
 cc     113    blah_p

Мне нужен запрос SQL, который возвращает это:

 COL1   COL2   COL3
 ---------------------
 aa     111    blah_x
 aa     111    blah_j
 bb     112    blah_d
 bb     112    blah_d

Answer 1

Это работает для вас?

select t.* from table t
left join ( select col1, col2, count(*) as count from table group by col1, col2 ) c on t.col1=c.col1 and t.col2=c.col2
where c.count > 1

Answer 2

С указанными вами данными ваш запрос невозможен. Данные по строкам 5 и 6 не различаются внутри себя.

Предполагая, что ваша таблица называется quux, если вы начинаете с чего-то вроде этого:

SELECT a.COL1, a.COL2, a.COL3 
FROM quux a, quux b
WHERE a.COL1 = b.COL1 AND a.COL2 = b.COL2 AND a.COL3 <> b.COL3
ORDER BY a.COL1, a.COL2

Вы получите этот ответ:

 COL1   COL2   COL3
 ---------------------
 aa     111    blah_x
 aa     111    blah_j

Это потому, что строки 5 и 6 имеют одинаковые значения для COL3. Любой запрос, который возвращает обе строки 5 и 6, также возвращает дубликаты ВСЕХ строк в этом наборе данных.

С другой стороны, если у вас есть первичный ключ (ID), вы можете использовать этот запрос вместо:

SELECT a.COL1, a.COL2, a.COL3
FROM quux a, quux b
WHERE a.COL1 = b.COL1 AND a.COL2 = b.COL2 AND a.ID <> b.ID
ORDER BY a.COL1, a.COL2

[Отредактировано для упрощения предложения WHERE]

И вы получите желаемый результат:

COL1   COL2   COL3
---------------------
aa     111    blah_x
aa     111    blah_j
bb     112    blah_d
bb     112    blah_d

Я только что проверил это на SQL Server 2000, но вы должны увидеть те же результаты на любой современной базе данных SQL.

Blorgbeard доказал, что я неправ - хорошо для него!

Answer 3

Присоединяйся к себе вот так:

SELECT a.col3, b.col3, a.col1, a.col2 
FROM tablename a, tablename b
WHERE a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 != b.col3

Если вы используете postgresql, вы можете использовать oid, чтобы он возвращал меньше дублированных результатов, например так:

SELECT a.col3, b.col3, a.col1, a.col2 
FROM tablename a, tablename b
WHERE a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 != b.col3
  AND a.oid < b.oid

Answer 4

У меня нет базы данных, чтобы проверить это, но я думаю, что она должна работать...

select
  *
from
  theTable
where
  col1 in
    (
    select
      col1
    from
      theTable
    group by
      col1||col2
    having
      count(col1||col2) > 1
    )

Answer 5

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

select COL1,COL2,COL3
from theTable a
where exists (select 'x'
              from theTable b
              where a.col1=b.col1
              and   a.col2=b.col2
              and   a.col3<>b.col3)
order by col1,col2,col3

Answer 6

Моя наивная попытка была бы

select a.*, b.* from table a, table b where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 != b.col3;

но это вернет все строки дважды. Я не уверен, как вы ограничите это, просто вернув их один раз. Возможно, если бы был первичный ключ, вы могли бы добавить "и a.pkey

Как я уже сказал, это не элегантно, и, вероятно, есть лучший способ сделать это.

Answer 7

Примерно так должно работать:

SELECT a.COL1, a.COL2, a.COL3
FROM YourTable a
JOIN YourTable b ON b.COL1 = a.COL1 AND b.COL2 = a.COL2 AND b.COL3 <> a.COL3

В общем случае предложение JOIN должно включать каждый столбец, который вы считаете частью "дубликата" (в данном случае COL1 и COL2), и как минимум один столбец (или столько, сколько требуется) для исключения объединения строк к себе (COL3, в данном случае).

Answer 8

Вот как вы найдете дубликаты. Проверено в Oracle 10g с вашими данными.

выберите * из tst, где (col1, col2) в (выберите col1, col2 из группы tst по col1, col2 с количеством (*) > 1)

Answer 9

Забудьте о присоединениях - используйте аналитическую функцию:

select col1, col2, col3
from
(
select col1, col2, col3, count(*) over (partition by col1, col2) rows_per_col1_col2
from table
)
where rows_per_col1_col2 > 1

Answer 10

Выберите COL1,COL2,COL3

из таблицы

сгруппировать по COL1,COL2,COL3

имея счет (*)>1