Найдите совпадающие первые 7 символов, чтобы идентифицировать дубликаты
Я пытаюсь определить дубликаты state_num, которые не проходят проверку. R вызывает проблемы с проверкой, но я хочу просто найти первые 7 символов и найти повторяющиеся значения, чтобы он возвращал строку, в которой есть R в строке, и строку, в которой этого нет. Столбец имеет тип: char(15) Но при попытке выполнить запрос он не находит совпадающих 7 символов. Моя таблица показывает только то, как она должна выглядеть, но не показывает, что на самом деле возвращается. По сути, это просто поиск состояния и только поиск не R state_num в результатах. Он должен возвращать около 480 строк, но возвращает около 20 тыс. строк, а не просто показывает дубликаты.
Я пробовал запрашивать кучу разных способов, но за последний час я смог вернуть строку R только в том случае, если я добавил AND state_num[8] = 'R' в конец запроса. Что побеждает то, что я пытаюсь найти повторяющиеся первые 7 символов. Это база данных Informix.
Мой запрос:
SELECT id_ref, cont_ref, formatted, state_num, type, state
FROM state_form sf1
WHERE EXISTS (select cont_ref, san
FROM state_form sf2
WHERE sf1.cont_ref = sf2.cont_ref and left(sf1.state_num,7) = LEFT(sf2.state_num,7)
GROUP BY cont_ref, state_num
HAVING COUNT(state_num) > 1)
AND state = 'MT';
Это то, что я хотел бы, чтобы мои результаты возвращались:
@jarlh спасибо, что указали на это. Я обновил псевдонимы. Вы правы, cont_ref всегда будет соответствовать. Я сужаю область поиска до определенного штата. Думаю, я в тупике, как я могу вытащить эти первые 7 символов и посмотреть, есть ли дубликаты. Я могу запросить все и экспортировать в Excel, чтобы найти, но не знаю, как это сделать с помощью SQL.
Имеет ли здесь значение «Р»? Вроде может он 1234567T и 1234567 тоже считаются дубликатом? Или бы про 7654321 и 76543210?
@Isolated no R не имеет значения, есть ли другие буквы, я просто хочу, чтобы они соответствовали первым 7 символам со следующим символом или без него. Он примет 15 символов, но беспокоится только о том, чтобы найти как 1234567, так и 1234567T или 1234567R.
@Isolated Думаю, это близко. левая функция в group by . Informix, похоже, не нравится. Получение стандартной синтаксической ошибки в левой функции. Я поищу, но я думаю, что это довольно близко.
Просто обновил ответ, если группа вызывает проблему.
@Isolated Я пытаюсь получить целевой state_num, который соответствует всему до 7-го положения, все, что после, в порядке и может вернуться. Я просто хочу иметь возможность просмотреть его. В этой таблице около 40 тыс. записей для этого состояния. Просто нужно иметь тот же cont_ref. Мы видели, что когда файл был прочитан, он импортировал букву после этого 7-го символа. Мы хотим идентифицировать и сделать rec_type недействительным = I, если у него есть дубликат с буквой. Я поиграю с тем, что вы показали. Я вижу, что рабочий пример возвращается, но cte не разрешено в informix. Спасибо за ваше время и ответы. Это продвинуло меня немного дальше.
@isolated У меня получилось, спасибо. Informix 11.x не поддерживает cte, а 14 позволяет.
Ответы 1
Вот несколько вариантов, дающих одинаковые результаты. Это может потребоваться изменить, если вам нужно идентифицировать 8-й символ как что-то вроде буквы. То есть это также поймает 12345678 и 1234567.
create table my_data (
id_ref integer,
cont_ref integer,
state_num varchar(20),
type varchar(5),
state varchar(5)
);
insert into my_data values
(1, 5237, '7175011R', 'Y', 'MT'),
(2, 5237, '7175011', 'I', 'MT'),
(3, 6789, '7878787', 'Y', 'CA'),
(4, 6789, '7878787R', 'I', 'CA'),
(5, 555678, '9967894', 'I', 'MT'),
(6, 555678, '9967894R', 'Y', 'MT'),
(7, 98765, '123456', 'I', 'MT');
Запрос №1
with dupes as (
select cont_ref
from my_data
where state = 'MT'
group by cont_ref, left(state_num, 7)
having count(*) > 1
)
select m.id_ref, m.cont_ref, m.state_num, m.type, m.state
from my_data m
join dupes d
on m.cont_ref = d.cont_ref;
Запрос №2
select m.id_ref, m.cont_ref, m.state_num, m.type, m.state
from my_data m
where m.cont_ref in (
select cont_ref
from my_data
where state = 'MT'
group by cont_ref, left(state_num, 7)
having count(*) > 1
);
ОБНОВИТЬ
Если Informix не хочет группировать по левому краю (столбец, 7), вы можете получить целевые значения cont_ref, используя это. Вот метод CTE, но вы также можете использовать подзапрос.
with dupes as (
select cont_ref
from (
select cont_ref, left(state_num, 7) as left_seven
from my_data
where state = 'MT'
)z
group by cont_ref
having count(*) > 1
)
select m.*
from my_data m
join dupes d
on m.cont_ref = d.cont_ref;
я пытаюсь получить цель state_num, которая соответствует всем до 7-й позиции, все, что после, в порядке. Просто нужно иметь тот же cont_ref
Совет сегодняшнего дня: определите все столбцы!
WHERE cont_ref = cont_refбудет (почти) всегда верным. (То есть, пока cont_ref не равен нулю.) Используйте разные псевдонимы столбцов для двух экземпляров state_form. Делайsf1.cont_ref = sf2.cont_ref.