Sasmi_Office Ответов: 2

Поиск уникальности человека с различной комбинацией электронной почты мобильного телефона в SQL.

Привет Друзья,

Нужно найти уникальность человека с идентификатором электронной почты и номером телефона в SQL.

Например, у нас есть данные, как показано ниже.

Телефон, Электронная Почта, Id
111, abc@gmail.com, 1
112, abc@gmail.com, 2
112, pqr@hotmail.com, 3
113, abc@gmail.com, 4
211, someoneelse@live.com, 5

Если вы проверите приведенные выше данные, то эти данные принадлежат двум людям с разными идентификаторами электронной почты и телефонными номерами, но человек один уникален, как найти эту уникальность в данных с помощью SQL-запроса.

Мне нужен вывод, как показано ниже, с новым столбцом человек, имеющий другой идентификатор электронной почты (Gmail, Hotmail) и телефоны (111,112,113), которые связаны друг с другом в каком-то месте номер телефона связан в другом идентификаторе электронной почты связан.

Человек, Телефон, Электронная Почта, Удостоверение Личности
1,111, abc@gmail.com, 1
1,112, abc@gmail.com, 2
1,112, pqr@hotmail.com, 3
1,113, abc@gmail.com, 4
2,211, someoneelse@live.com, 5

Пожалуйста, требуйте скорейшего ответа, так как тратите много времени.

Спасибо и с уважением,
Сантош Савант

Что я уже пробовал:

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

2 Ответов

Рейтинг:
0

kosmas kafataridis

Я не могу объяснить это пожалуйста попробуйте и если нет я попробую

CREATE TABLE #test (test1 nvarchar(50),test2 nvarchar(50),test3 int);
CREATE TABLE #uniquephone(ID bigint IDENTITY(1,1) NOT NULL, phone nvarchar(50));
CREATE TABLE #uniquemail(ID bigint IDENTITY(1,1) NOT NULL, mail nvarchar(50));
CREATE TABLE #uniqueall(ID bigint IDENTITY(1,1) NOT NULL, id2 bigint);
CREATE TABLE #uniqueall2uniquephone(ID bigint NOT NULL, id2 bigint);
insert into #test (test1, test2, test3) values ('111', 'abc@gmail.com', 1);
insert into #test (test1, test2, test3) values ('112', 'abc@gmail.com', 2);
insert into #test (test1, test2, test3) values ('112', 'pqr@hotmail.com', 3);
insert into #test (test1, test2, test3) values ('113', 'abc@gmail.com', 4);
insert into #test (test1, test2, test3) values ('211', 'someoneelse@live.com', 5);
insert into #uniquephone (phone)  select distinct test1 from #test;
insert into #uniquemail (mail)  select distinct test2 from #test;
insert into #uniqueall (id2)  select distinct min(id2) id2 from (select a.id, c.id id2, test1, test2, test3 from #uniquephone a inner join #test on a.phone=#test.test1 inner join #uniquemail c on #test.test2=c.mail) a group by id
insert into #uniqueall2uniquephone (ID, id2)  select distinct min(id2) id2, id from (select a.id, c.id id2, test1, test2, test3 from #uniquephone a inner join #test on a.phone=#test.test1 inner join #uniquemail c on #test.test2=c.mail) a group by id
select a.ID, #test.* from #uniqueall a inner join #uniqueall2uniquephone b on a.id2=b.ID inner join #uniquephone c on b.id2=c.ID inner join #test on c.phone=#test.test1 
drop table #test  
drop table #uniquephone  
drop table #uniquemail  
drop table #uniqueall  
drop table #uniqueall2uniquephone

#тест-это ваша таблица
outut:
ID	test1	test2	test3
1	111	abc@gmail.com	1
1	112	abc@gmail.com	2
1	112	pqr@hotmail.com	3
1	113	abc@gmail.com	4
2	211	someoneelse@live.com	5

Рейтинг:
0

CHill60

Вот альтернатива решению 1, которому, как мне кажется, легче следовать. Я создал тестовые данные следующим образом:

create table #test 
(id int identity(1,1),phone nvarchar(123),Email nvarchar(123),Person int)

insert into #test (phone, Email) values
('111', 'abc@gmail.com'), 
('112', 'abc@gmail.com'), 
('112', 'pqr@hotmail.com'), 
('113', 'abc@gmail.com'), 
('211', 'someoneelse@live.com')
Сначала используйте функцию SQL Window, чтобы дать нам предлагаемые номера "персон", основанные только на адресе электронной почты:
UPDATE T SET Person = D.RN FROM #test T
INNER JOIN 
  (SELECT id, DENSE_RANK() OVER (ORDER BY Email) AS RN FROM #test) D on D.id = T.ID
Это содержание #test после этого: 
id	Phone	Email			Person
1	111	abc@gmail.com		1
2	112	abc@gmail.com		1
3	112	pqr@hotmail.com		2
4	113	abc@gmail.com		1
5	211	someoneelse@live.com	3
Это следующее утверждение исправляет ситуацию для телефонного номера ... то есть оно заменит Person = 2 на Person = 1, потому что это один и тот же "человек". Мне доводилось пользоваться MIN чтобы получить более раннее Person номер но вы могли бы так же легко использовать MAX :
UPDATE T SET Person = newPerson
from (
     SELECT PHONE, min(person) as newPerson FROM #test 
     GROUP BY phone having count(*) > 1) AS q
inner join #test t on q.phone=t.phone
Содержание #test после этого:
id	Phone	Email			Person
1	111	abc@gmail.com		1
2	112	abc@gmail.com		1
3	112	pqr@hotmail.com		1
4	113	abc@gmail.com		1
5	211	someoneelse@live.com	3
Вы можете просто остановиться на этом, так как у каждого человека есть уникальное число (1 и 3), но если вы действительно хотите, чтобы числа были последовательными, то запустите эту последнюю часть. Это изменение каждого отдельного Person с помощью RANK но вы могли бы так же легко использовать ROW_NUMBER()
UPDATE T SET Person = Q2.NP 
FROM #test T
INNER JOIN (select Person, RANK() OVER (ORDER BY Person) AS NP FROM (select distinct Person from #test) Q1) Q2 ON Q2.Person=T.Person
Окончательные результаты таковы
id	Phone	Email			Person
1	111	abc@gmail.com		1
2	112	abc@gmail.com		1
3	112	pqr@hotmail.com		1
4	113	abc@gmail.com		1
5	211	someoneelse@live.com	2
Вот полный код, выполненный в виде однострочных строк, чтобы он выглядел короче ;-)
create table #test (id int identity(1,1),phone nvarchar(123),Email nvarchar(123),Person int)
insert into #test (phone, Email) values
('111', 'abc@gmail.com'), 
('112', 'abc@gmail.com'), 
('112', 'pqr@hotmail.com'), 
('113', 'abc@gmail.com'), 
('211', 'someoneelse@live.com')
UPDATE T SET Person = D.RN FROM #test T INNER JOIN ( SELECT id, DENSE_RANK() OVER (ORDER BY Email) AS RN FROM #test) D on D.id = T.ID
UPDATE T SET Person = newPerson from (SELECT PHONE, min(person) as newPerson FROM #test GROUP BY phone having count(*) > 1) AS q inner join #test t on q.phone=t.phone
UPDATE T SET Person = Q2.NP FROM #test T INNER JOIN (select Person, ROW_NUMBER() OVER (ORDER BY Person) AS NP FROM (select distinct Person from #test) Q1) Q2 ON Q2.Person=T.Person
select * from #TEST
DROP TABLE #test