只返回值出现超过n次的行。

Suppose I have a table

假设我有一张桌子

  id            value
------        ---------
  A              123
  A              422
  B              441
  B              986
  B              674
  C              648

I need a query which will return only those id's which have 3 or more values associated with them. So, in that case it will only return B. Thank you.

我需要一个查询，它只返回那些具有3个或更多值的id。这样的话，它只会返回b。谢谢。

4 个解决方案

#1

Use the Group By clause with Having:

使用Group By子句并具有:

SELECT id 
FROM dbo.TableName
GROUP BY ID
HAVING COUNT(*) >= 3

Demo

演示

#2

In case you want to include the value, you can use window functions to find the ones with three or more rows, e.g.:

如果您想要包含该值，您可以使用window函数来查找具有三个或更多行的函数，例如:

DECLARE @x TABLE(id CHAR(1), value INT);

INSERT @x SELECT 'A',       123;
INSERT @x SELECT 'A',       422;
INSERT @x SELECT 'B',       441;
INSERT @x SELECT 'B',       986;
INSERT @x SELECT 'B',       674;
INSERT @x SELECT 'C',       648;

;WITH x AS 
(
  SELECT id, value, rn = ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)
  FROM @x
)
SELECT id FROM x WHERE rn = 3;

And you can change the ORDER BY to help better determine which value is included.

您可以通过改变顺序来帮助更好地确定包含哪些值。

#3

select id from table group by id having (count(id) >=3)

通过id (count(id) >=3)从表组中选择id

#4

SELECT ID
FROM TABLE
GROUP BY ID
HAVING COUNT(*) >= 3

#1