SQL查询获取不止一次的值。

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.

我需要查询我的数据库，以显示表中的记录，其中lastname发生了三次以上。在我的学生表上，有三个人姓史密斯，四个姓约翰逊，一个姓波特。我的查询应该显示具有lastname 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()，但我似乎想不出如何应用它。

6 个解决方案

#1

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

#2

From Oracle (but works in most SQL DBs):

来自Oracle(但适用于大多数SQL DBs):

SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
HAVING COUNT(*) >= 3

P.S. it's faster one, because you have no Select withing Select methods here

另外，它更快，因为这里没有选择withing选择方法。

#3

For MySQL:

MySQL:

SELECT lastname AS ln 
    FROM 
    (SELECT lastname, count(*) as Counter 
     FROM `students` 
     GROUP BY `lastname`) AS tbl WHERE Counter > 2

#4

SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
ORDER BY COUNT(*) DESC

#5

The answers mentioned here is quite elegant https://*.com/a/6095776/1869562 but 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://*.com/a/6095776/1869562，但是经过测试，我发现它只返回姓氏。如果您想返回整个记录本身呢?这样做(Mysql)

SELECT *
FROM `beneficiary`
WHERE `lastname`
IN (

  SELECT `lastname`
  FROM `beneficiary`
  GROUP BY `lastname`
  HAVING COUNT( `lastname` ) >1
)

#6

For postgresql:

postgresql的:

SELECT * AS rec 
FROM (
    SELECT lastname, COUNT(*) AS counter 
    FROM students 
    GROUP BY lastname) AS tbl 
WHERE counter > 1;

#1