SQL查询获取多次出现的值

发布于 2024-11-09 07:33:02 字数 194 浏览 3 评论 0原文

我需要查询数据库以显示表中姓氏出现超过 3 次的记录。示例：在我的学生表中，有 3 个姓氏为“Smith”的人，4 个姓氏为“Johnson”，1 个姓氏为“Potter”。我的查询应该显示姓氏为 Smith 和 Johnson 的记录，因为这些值出现的次数超过或等于 3 次。

谁能指点我这一点吗？我正在考虑使用 COUNT() 但我似乎不知道如何应用它？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

っ左 2024-11-16 07:33:02

来自 Oracle（但适用于大多数 SQL 数据库）：

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

PS 它更快，因为这里没有使用 Select 方法进行选择

From Oracle (but works in most 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

回复收藏 0 原文

叶落知秋 2024-11-16 07:33:02

对于 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

For 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

回复收藏 0 原文

调妓 2024-11-16 07:33:02

对于 MySQL：

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

For MySQL:

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

回复收藏 0 原文

撩心不撩汉 2024-11-16 07:33:02

这里提到的答案非常优雅 https://stackoverflow.com/a/6095776/1869562 但经过测试，我意识到它只返回姓氏。
如果您想返回整个记录本身怎么办？
这样做
（对于 MySQL）

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

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

The answers mentioned here is quite elegant https://stackoverflow.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)

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

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

回复收藏 0 原文

清风夜微凉 2024-11-16 07:33:02

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

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

回复收藏 0 原文

喜爱纠缠 2024-11-16 07:33:02

对于 PostgreSQL：

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

For postgresql:

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

回复收藏 0 原文

弥繁 2024-11-16 07:33:02

我认为这个答案也可以工作（不过可能需要一些修改）：

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)

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)

回复收藏 0 原文

~没有更多了~