SQL：选择列值至少出现 N 次的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5760335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Select rows with a column value that occurs at least N times?
提问by Vivian River
Suppose I have a SQL table "Celebrities" with two columns: "fname" and "lname":
假设我有一个包含两列的 SQL 表“Celebrities”：“fname”和“lname”：
fname | lname
---------+-------
Bill | Clinton
Bill | Gates
George | Bush
George | Clinton
Barack | Obama
I would like to write a query that returns the first and last name of each person in the table whose last name appears at least twice in the column "lname". How do I write this SQL query?
我想编写一个查询，返回表中每个人的名字和姓氏，其姓氏在“lname”列中至少出现两次。如何编写此 SQL 查询？
回答by Ocaso Protal
SELECT fname, lname FROM Celebrities
WHERE lname IN
(SELECT lname FROM Celebrities
GROUP BY lname HAVING COUNT (lname) >1)
回答by OMG Ponies
Using a 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
Using 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)
回答by Mikael Eriksson
select fname, lname
from
(
select fname, lname, count(*) over(partition by lname) as lcount
from Celebrities
) as S
where lcount > 1
Tested in SQL Server 2008. Might work in other DBMS that support count(*) over(...)
在 SQL Server 2008 中测试。可能在其他支持的 DBMS 中工作 count(*) over(...)
