Получить строки, где Column1 != Несколько значений из Column2
У меня есть 2 столбца местоположения. Мне нужно найти способ показать только исключения, где Location1 != Location2, НО есть несколько значений в столбце Location2, которые могут быть связаны со значением в Lcoation1.
Например: Сан-Диего в столбце Location1 может иметь 4 возможных значения в столбце Location2 (Север, Центр, Юг, Сан-Диего), и такие строки необходимо исключить из выполнения. Если Сан-Диего в столбце Location1 и Сиэтл (например) в столбце Location2, это считается исключением.
create table Locations
(Id INT, Location1 VARCHAR(30), Location2 VARCHAR(30));
INSERT INTO Locations
VALUES
(1, 'San Diego', 'North'),
(2, 'San Diego', 'South'),
(3, 'San Diego', 'Central'),
(4, 'San Diego', 'Arizona'),
(5, 'San Diego', 'San Diego'),
(6, 'San Diego', 'Seattle'),
(7, 'San Diego', 'North'),
(8, 'San Diego', 'San Diego'),
(9, 'San Diego', 'Central'),
(10, 'San Diego', 'South')
SELECT * FROM Locations
Я могу исключить только очевидное условие, когда Location 1 = Location 2:
SELECT *
FROM Locations
WHERE Location1 != Location2
Ожидаемый результат:
| Идентификатор | Lпокрытие1 | Местоположение2 |
|---|---|---|
| 4 | Сан Диего | Аризона |
| 6 | Сан Диего | Сиэтл |
Извините, я удалил свой комментарий ... Я все еще думаю, что вам нужно что-то, чтобы систематически связывать Location1 с Location2. Под жестким кодированием я имел в виду сам SQL-запрос, который лично мне не хотелось бы делать. Я предполагаю, что это причина того, что я никогда (хорошо, очень редко) разрешаю вводить мастер-данные во ввод произвольного текста... Вы можете предотвратить такие проблемы, ограничив то, что пользователю разрешено вводить. Однако на данный момент кажется, что это потребует некоторой перестройки архитектуры и (вероятно, ручной) очистки данных.
Ответы 2
WITH TIES в сочетании с оконной функцией sum() over() здесь может быть вариант
Пример
SELECT Top 1 with ties *
FROM Locations
Order By SUM(1) OVER (PARTITION BY LOCATION2)
Полученные результаты
Id Location1 Location2
4 San Diego Arizona
6 San Diego Seattle
Примечание:PARTITION BY может включать LOCATION1 ... Трудно сказать по вашим примерам данных:
... SUM(1) OVER (PARTITION BY LOCATION1,LOCATION2)
Это круто, и работает с результирующим набором (спасибо за урок WITH TIES!). Но мне интересно, возможно ли в реальном мире, скажем, «Юг» присутствовать только один раз...
@squillman Хороший вопрос. Трудно понять, что на самом деле делает ОП с такой маленькой выборкой.
Это работало для этого примера набора данных, однако, когда я исключаю последнюю строку с югом в Location2 (чтобы сохранить только 1 запись о нем), он обрабатывает ее как исключение. Я думаю, мне нужно жестко закодировать это как-то...
@ Yara1994 Трудно понять, какова твоя цель. Чтобы жестко закодировать исключения или найти исключения для проверки.
Если вы хотите выполнить некоторое ручное (текущее) обслуживание, вы можете создать таблицу сопоставления действительных отношений Location1-Location2 и отфильтровать Locations на основе таблицы сопоставления. Таким образом, ты определяет, что является допустимым, и вы можете легко найти то, что не является таковым.
DROP TABLE IF EXISTS locations;
CREATE TABLE Locations
(Id INT, Location1 VARCHAR(30), Location2 VARCHAR(30));
DROP TABLE IF EXISTS RegionMap;
CREATE TABLE RegionMap (Location1 VARCHAR(30), Location2 VARCHAR(30));
INSERT dbo.RegionMap (Location1, Location2)
VALUES ('San Diego', 'North'),
('San Diego', 'South'),
('San Diego', 'Central'),
('San Diego', 'San Diego');
INSERT INTO Locations
VALUES
(1, 'San Diego', 'North'),
(2, 'San Diego', 'South'),
(3, 'San Diego', 'Central'),
(4, 'San Diego', 'Arizona'),
(5, 'San Diego', 'San Diego'),
(6, 'San Diego', 'Seattle'),
(7, 'San Diego', 'North'),
(8, 'San Diego', 'San Diego'),
(9, 'San Diego', 'Central');
SELECT l.Location1, l.Location2
FROM locations l
LEFT JOIN dbo.RegionMap m ON m.Location1 = l.Location1
AND m.Location2 = l.Location2
WHERE m.Location2 IS NULL;
Обратите внимание, что я удалил ID 10 (второй пример Южного Сан-Диего), и вы по-прежнему получаете правильные результаты.
Недостаток здесь в том, что вам придется установить процесс для обслуживания таблицы сопоставления. Если вы регулярно запускаете этот запрос и получаете регулярный отчет о результатах, это поможет вам узнать, что вам может понадобиться добавить в таблицу сопоставления.
Но на самом деле лучше всего в первую очередь ограничить то, что пользователи могут вводить в качестве мастер-данных...
@squillman, в том-то и дело. Ассоциации нет. Представьте, что у каждого сотрудника есть 2 адреса местоположения (расположение офиса и расположение рабочей зоны). Такие данные вводятся вручную другим сотрудником. Чтобы исключить человеческую ошибку, когда кто-то может случайно назначить Сан-Диего в качестве офиса, а Сиэтл в качестве рабочей зоны, я хочу построить что-то, что будет ловить такие вещи. Если мне нужно вручную жестко запрограммировать его, нет проблем. Благодарю вас