SQL:选择具有至少N次出现的列值的行? [英] SQL: Select rows with a column value that occurs at least N times?

问题描述

假设我有一个SQL表"Celebrities"，其中有两列:"fname"和"lname":

Suppose I have a SQL table "Celebrities" with two columns: "fname" and "lname":

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

我想编写一个查询，该查询返回表中每个人的名字和姓氏，这些人的姓氏在"lname"列中至少出现两次.如何编写此SQL查询?

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?

推荐答案

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

这篇关于SQL:选择具有至少N次出现的列值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！