SQL: выберите строки со значением столбца, которое встречается не менее N раз?

Предположим, что у меня есть таблица SQL "Знаменитости" с двумя столбцами: "fname" и "lname":

fname    | lname    
---------+-------  
Bill     | Clinton
Bill     | Gates
George   | Bush
George   | Clinton
Barack   | Obama

Я хотел бы написать запрос, который возвращает имя и фамилию каждого человека в таблице, фамилия которого появляется как минимум дважды в столбце "lname". Как написать этот SQL-запрос?

Ответ 1

SELECT fname, lname FROM Celebrities 
WHERE lname IN 
 (SELECT lname FROM Celebrities 
  GROUP BY lname HAVING COUNT (lname) >1)

Ответ 2

Использование JOIN:

SELECT a.*
  FROM CELEBRITIES a
  JOIN (SELECT c.lname
          FROM CELEBRITIES c
      GROUP BY c.lname
        HAVING COUNT(*) >= 2) b ON b.lname = a.lname

Использование EXISTS:

SELECT a.*
  FROM CELEBRITIES a
 WHERE EXISTS (SELECT NULL
                 FROM CELEBRITIES c
                WHERE c.lname = a.lname
             GROUP BY c.lname
               HAVING COUNT(*) >= 2)

Ответ 3

select fname, lname
from 
  (
    select fname, lname, count(*) over(partition by lname) as lcount
    from Celebrities
  ) as S
where lcount > 1

Протестировано в SQL Server 2008. Может работать в других СУБД, поддерживающих count(*) over(...)