SQL Server: выберите только одну строку на основе поля при наличии нескольких строк

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть таблица с 3 столбцами: Name , Surname , Email . Данные в этих столбцах не уникальны.

Мне нужно получить результат, соответствующий следующим критериям:

Выберите все три столбца
Записи электронной почты должны быть уникальными
В электронном письме должна быть только одна запись

Это означает, что SELECT DISTINCT неприменимо, поскольку оно может извлекать несколько записей электронной почты.

Есть идеи?

1. вы можете попробовать с GROUP BY Email, Name, Surname

2. SQL предпочитает, чтобы вы предоставляли правило, а не ему приходилось угадывать, что вы хотите, чтобы оно делало — поэтому, даже если вам «все равно», вам обычно приходится указывать правило, как выбрать 1 строку из многих — так, должен ли он, например, выбирать строку со значениями Name и Surname , которые сортируют самым ранним или последним, или использовать какой-либо другой столбец в качестве критерия?

3. Согласовано с вышеизложенным. Вероятно, должно быть поле sequence или effdt, указывающее порядок, в котором разные имена стали ассоциироваться с адресом электронной почты. Таким образом, вы можете выбрать самое последнее имя, связанное с электронным письмом, или самое раннее, или любое другое, исходя из того, что вы действительно хотите, в отличие от простого выбора случайного имени из шапки.

Ответ №1:

Вы не указали свою СУБД, но большинство систем поддерживают «Оконные агрегированные функции»:

 with cte as
 ( select Email, Name, Surname,
      row_number() over (partition by Email order by Name) as rn
   from tab
 )
select Email, Name, Surname
from tab
where rn = 1

Это присваивает рейтинг каждому электронному письму и возвращает только первое.

Ответ №2:

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

При использовании MySQL (вы не указали базу данных):

 select group_concat(distinct name order by name separator ', ') as names,
       group_concat(distinct surename order by name separator ', ') as surenames,
       email
  from tbl
 group by email

Если используется PostgreSQL, string_agg является эквивалентом. Если используете Oracle, используйте listagg.

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

Однако, если ваша база данных не поддерживает предложение with или функции window (т.е. MySQL), вы можете использовать приведенное ниже, чтобы произвольно показывать только одно имя и фамилию в электронном письме:

 select x.*, y.surname
  from (select email, max(name) as name from tbl group by email) x
  join tbl y
    on x.name = y.name
   and x.email = y.email

Это покажет правильную фамилию для данного имени, потому что сначала выбирается максимальное значение (name), а затем получает фамилию для этого имени и адрес электронной почты.