SQL：where子句中的明显计数？

My order table looks like this:

我的订单表看起来像这样：

id   fk_customer
1    34
2    34
3    34
4    7
5    7
6    8

I would like to select only those customers who have more than 2 orders.

我想只选择订单超过2个的客户。

This doesnt work however:

但这不起作用：

SELECT * FROM order WHERE COUNT DISTINCT fk_customer > 2

SELECT * FROM order WHERE COUNT DISTINCT fk_customer> 2

Please advice!

请指教！

thanks

谢谢

4 个解决方案

#1

Try this:

尝试这个：

SELECT fk_customer, COUNT(*)   
FROM dbo.[Order]
GROUP BY fk_customer
HAVING COUNT(*) > 2

Order is a reserved word in most SQL based database systems - bad choice for a table name.... in SQL Server, you need to put it in square brackets to make it clear it's an object name (not the reserved word) to be used.

在大多数基于SQL的数据库系统中，顺序是一个保留字 - 对于表名称的错误选择....在SQL Server中，您需要将其放在方括号中以使其清楚它是一个对象名（而不是保留字）用过的。

#2

Try this

尝试这个

SELECT fk_customer FROM orders
GROUP BY fk_customer
HAVING COUNT(id) > 2

#3

If you try to use COUNT in the WHERE clause, SQL will throw an exception. You can only use an aggregate function if it is within a sub-query contained in a HAVING clause or a select list.

如果您尝试在WHERE子句中使用COUNT，SQL将引发异常。如果聚合函数位于HAVING子句或选择列表中包含的子查询中，则只能使用聚合函数。

SELECT fk_customer, COUNT(*)    
  FROM dbo.[Order] 
 GROUP 
    BY fk_customer 
HAVING COUNT(*) > 2

#4

SELECT fk_customer, COUNT(fk_customer) FROM [order]
GROUP BY fk_customer HAVING COUNT(*) > 2

#1