SQL Query question – Как выбрать группы строк, используя общие черты

Я работаю с данными переписи, и я хочу предоставить возможность поиска записей, предоставив список имен. Идея состоит в том, что если вы знаете имя или 2 или 3 члена семьи, вы сможете исключить все адреса, у которых нет этих имен. Рассмотрим этот пример набора данных (Адрес, Фамилия, Имя):


«Жители дома 2 в Нижнем Нижнем (Корк № 4 Городской (часть), Корк)», «Гэмбл», «Джулия»,
«Жители дома 2 в Нижнем Нижнем (Корк № 4 Городской (часть), Корк)», «Гэмбл», «Ричард»,
«Жители дома 2 в Нижнем Нижнем (Корк № 4 Городской (часть), Корк)», «Гэмбл», «Ханна»,
«Жители дома 2 в Нижнем Нижнем (Корк № 4 Городской (часть), Корк)», «Гэмбл», «Хелен»,


Поиск Джулии, Ханны и Хелен должен иметь возможность вернуть все 4 строки, поскольку они имеют общий адрес. Это звучит достаточно просто, но у меня проблемы с этим. Из-за размера набора данных курсоры выходят из строя. Есть идеи?

(Излишне говорить, что я упростил это несколько, так как я игнорирую часть названия фамилии для поиска)

Этот запрос:

select streetaddress, count(*) as occupantcount from census where firstname in ("Julia", "Hannah", "Helen") group by streetaddress order by occupantcount desc 

будет возвращать адрес и количество пассажиров по каждому адресу, где один из участников имеет одно из первых имен в списке IN () и заказывает результаты в заказе большинства пассажиров и наименьших пассажиров. Вариации по этому же принципу (группировка по адресам) могут предоставить вам другие виды информации. Вы можете применить последовательные фильтры к результатам до нуля в нужных строках.

Это реляционная проблема divisio n.

 SELECT Address, FamilyName, Forename FROM YourTable WHERE Address IN (SELECT Address FROM YourTable WHERE Forename IN ( 'Julia', 'Hannah', 'Helen' ) GROUP BY Address HAVING COUNT(DISTINCT Forename) = 3) 

Или

 WITH Names(name) AS (SELECT 'Julia' UNION ALL SELECT 'Hannah' UNION ALL SELECT 'Helen') SELECT Address, FamilyName, Forename FROM YourTable y1 WHERE NOT EXISTS (SELECT * FROM Names n WHERE NOT EXISTS(SELECT * FROM YourTable y2 WHERE y1.Address = y2.Address AND y2.Forename = n.Name)) 

Вы можете попробовать следующее:

 SELECT A.* FROM YourTable A JOIN ( SELECT Address, COUNT(*) Quant FROM YourTable WHERE Forename IN ('Julia','Hannah','Helen') GROUP BY Address HAVING COUNT(DISTINCT Forename) > 2) B ON A.Address = B.Address 

Если вы указали имя и адрес, это должно быть простым и быстрым:

 SELECT a.Address as CommonAddress FROM (SELECT Address FROM Names WHERE Forename = 'Julia') a INNER JOIN (SELECT Address FROM Names WHERE Forename = 'Richard') b ON a.Address=b.Address INNER JOIN (SELECT Address FROM Names WHERE Forename = 'Helen') c on b.Address=c.Address 

Решением является использование подзапроса для поиска адреса, который является общим для всех предоставленных имен, и для возврата всех записей по этому адресу.

 declare @people table ( address varchar(255), familyName varchar(255), forename varchar(255) ) insert into @people values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Julia') insert into @people values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Richard') insert into @people values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Hannah') insert into @people values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Helen') insert into @people values ('Residents of a house 2 somewhere else (Cork No. 4 Urban (part of), Cork)', 'Cooper', 'Helen') select people.* from @people as people where people.address in ( select address from @people where forename in ('Julia', 'Hannah', 'Helen') group by address having count(forename) >= 3 -- This must be equal to the number of names searched for ) 

Одна из проблем с этим решением состоит в том, что, если есть три Ханны, живущие по одному адресу, запрос вернет этих людей, даже если Джулия и Хелен не будут там жить.

 SELECT t.address , t.familyname , t.forename FROM yourTable t WHERE t.address IN -- search subquery ( SELECT s1.address FROM yourTable s1 JOIN yourTable s2 ON s2.address = s1.address JOIN yourTable s3 ON s3.address = s1.address WHERE s1.forename = "Julia" AND s2.forename = "Hannah" AND s3.forename = "Helen" ) ORDER BY t.address , t.familyname , t.forename ; 

Второе решение:

 SELECT t.address , t.familyname , t.forename FROM yourTable t WHERE EXISTS -- search subquery ( SELECT * FROM yourTable s1 JOIN yourTable s2 ON s2.address = s1.address JOIN yourTable s3 ON s3.address = s1.address WHERE s1.forename = "Julia" AND s2.forename = "Hannah" AND s3.forename = "Helen" AND s1.address = t.address ) ORDER BY t.address , t.familyname , t.forename ; 

Третье решение:

 SELECT t.address , t.familyname , t.forename FROM yourTable t WHERE -- search subqueries EXISTS ( SELECT * FROM yourTable s1 WHERE s1.forename= "Julia" AND s1.address = t.address ) AND EXISTS ( SELECT * FROM yourTable s2 WHERE s2.forename = "Hannah" AND s2.address = t.address ) AND EXISTS ( SELECT * FROM yourTable s3 WHERE s3.forename = "Helen" AND s3.address = t.address ) ORDER BY t.address , t.familyname , t.forename ; 
 DECLARE @namecount int; DECLARE @forenames TABLE (name varchar(50)); INSERT INTO @forenames VALUES ('...'), ('...'), ('...'); SELECT @namecount = COUNT(*) FROM @forenames; /* list all people by addresses that are shared by people whose forenames are included in @forenames */ SELECT cd.* FROM CensusData cd INNER JOIN ( SELECT d.Address FROM CensusData d INNER JOIN @forenames f ON d.Forename = f.Name GROUP BY d.Address HAVING COUNT(DISTINCT d.Forename) >= @namecount ) filter ON cd.Address = filter.Address /* same for family names */ SELECT cd.* FROM CensusData cd INNER JOIN ( SELECT d.[Family Name] FROM CensusData d INNER JOIN @forenames f ON d.Forename = f.Name GROUP BY d.Address HAVING COUNT(DISTINCT d.Forename) >= @namecount ) filter ON cd.[Family Name]= filter.[Family Name] /* and so on fro other criteria */ 

Вы также можете комбинировать критерии, если это необходимо.

 select * from dataset where address = ( select address from dataset where famly_name = 'Hannah' and forename = 'Gamble' ) 

Вы можете начать с чего-то вроде этого, предполагая, что адрес должен быть точно согласован. Его нужно настроить, так как вы, скорее всего, увидите некоторые дубликаты

 Select T1.* From TableName T1 Inner Join TableName T2 On T1.Address = T2.Address Where T1.ForeName = 'Julia' 
Interesting Posts

Что-нибудь вроде прокси-сервера SQL Server, который будет кэшировать данные, доступные локально?

Невозможно получить нулевые значения для подсчета в месяц

Как объединить несколько строк одного столбца в SQL Server 2005?

Не удается найти объект «QueryNotificationErrorsQueue», потому что он не существует или у вас нет разрешений

Как использовать внешний ключ в составе составного первичного ключа в Entity Framework?

База данных по дизайну – лучшая практика

vb.net искать базу данных sql для проверки номера учетной записи клиента

Какой из них лучше всего Посмотреть или сохранить в SQL Server для целей доступа к данным

Вставка хранимой процедуры SQL C # в

Оператор CASE SQL Server в разделе Where of Select

Динамический поворот Не отображает правый вывод

Как эффективно использовать SELECT таблицы вложенной зависимости, используя JOIN в SQL Server

Условное соединение SQL-сервера

Назначение имен таблицам в наборе результатов SQL Server

Как я могу контролировать сохраненные процедуры при использовании flywaydb?

Давайте будем гением компьютера.