获取多次发生的值的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6095567/
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 Query To Obtain Value that Occurs more than once
提问by Smiley
I need to query my database to show the records inside my table where lastname occurs more than three times. Example: in my Students Table, there are 3 people with Lastname 'Smith', 4 with 'Johnson', and 1 with 'Potter'. My query should show the records of those with the lastnames Smith, and Johnson since these values occur more than or equal to 3 times.
我需要查询我的数据库以显示我的表中姓氏出现 3 次以上的记录。示例：在我的学生表中，有 3 个人姓为“Smith”，4 个人姓为“Johnson”，1 个人姓为“Potter”。我的查询应该显示姓氏为 Smith 和 Johnson 的记录，因为这些值出现的次数超过或等于 3 次。
Can anyone point me to this? I was thinking of using COUNT() but I can't seem to think how to apply it?
谁能指出我这一点？我正在考虑使用 COUNT() 但我似乎无法考虑如何应用它？
回答by Martin Smith
For SQL Server 2005+
对于 SQL Server 2005+
;WITH T AS
(
SELECT *,
COUNT(*) OVER (PARTITION BY Lastname) as Cnt
FROM Students
)
SELECT * /*TODO: Add column list. Don't use "*" */
FROM T
WHERE Cnt >= 3
回答by Andrii Kovalchuk
From Oracle (but worksin most SQL DBs):
来自 Oracle（但适用于大多数 SQL DB）：
SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
HAVING COUNT(*) >= 3
P.S. it's fasterone, because you have no Select withing Select methods here
PS 它更快，因为这里没有 Select 和 Select 方法
回答by Till
For MySQL:
对于 MySQL：
SELECT lastname AS ln
FROM
(SELECT lastname, count(*) as Counter
FROM `students`
GROUP BY `lastname`) AS tbl WHERE Counter > 2
回答by garnertb
SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
ORDER BY COUNT(*) DESC
回答by Kudehinbu Oluwaponle
The answers mentioned here is quite elegant https://stackoverflow.com/a/6095776/1869562but upon testing, I realize it only returns the last name. What if you want to return the entire record itself ? Do this (For Mysql)
这里提到的答案非常优雅https://stackoverflow.com/a/6095776/1869562但经过测试，我意识到它只返回姓氏。如果您想返回整个记录本身怎么办？这样做（对于Mysql）
SELECT *
FROM `beneficiary`
WHERE `lastname`
IN (
SELECT `lastname`
FROM `beneficiary`
GROUP BY `lastname`
HAVING COUNT( `lastname` ) >1
)
回答by emirb
For postgresql:
对于 postgresql：
SELECT * AS rec
FROM (
SELECT lastname, COUNT(*) AS counter
FROM students
GROUP BY lastname) AS tbl
WHERE counter > 1;
回答by Maryam
I think this answer can also work (it may require a little bit of modification though) :
我认为这个答案也可以（不过可能需要稍作修改）：
SELECT * FROM Students AS S1 WHERE EXISTS(SELECT Lastname, count(*) FROM Students AS S2 GROUP BY Lastname HAVING COUNT(*) > 3 WHERE S2.Lastname = S1.Lastname)
