MySQL 获取某个字段，其中该字段存在>表中 x 次

发布于 2024-09-25 15:21:29 字数 285 浏览 3 评论 0原文

我正在尝试在 MySQL 中执行以下操作：

SELECT DISTINCT field
FROM table
WHERE COUNT(field) > 10

失败并显示： 1111 - 组函数的使用无效（据我了解，您不能在 where 子句中使用诸如 COUNT 之类的组函数？）

选择（不同）至少 N 行中存在的所有字段的正确方法是什么？这是我必须在外部用 PHP 做的事情吗？

谢谢！

原文

I'm trying to do the following in MySQL:

SELECT DISTINCT field
FROM table
WHERE COUNT(field) > 10

Which fails with: 1111 - Invalid use of group function (from what I understand, you can't use group functions such as COUNT in the where clause?)

What is the proper way of selecting (distinct) all fields which are present in at least N rows? Is this something I'll have to do externally with say, PHP?

Thanks!

分享到QQ

分享到微博

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

发布评论

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

番薯 2024-10-02 15:21:29

use：

SELECT DISTINCT field
  FROM table
HAVING COUNT(field) > 10

您不能在 WHERE 子句中的子查询之外使用聚合函数。这就是为什么您需要使用 HAVING 子句。另外请记住COUNT 计数非空值...

理想情况下，还应该有一个 GROUP BY 子句。但是 MySQL 对此相当宽松。

要比较所有列

...您必须添加一个GROUP BY子句来列出所有这些列 - 没有在这种情况下的简写。然后将 HAVING 子句更改为 COUNT(*) > 10..

use:

SELECT DISTINCT field
  FROM table
HAVING COUNT(field) > 10

You can't use aggregate functions outside of a subquery in the WHERE clause. Which is why you need to use the HAVING clause. Also keep in mind that COUNT counts non-null values...

Ideally, there should be a GROUP BY clause as well. But MySQL is rather lax about that.

To compare all the columns

...you're going to have to add a GROUP BY clause that lists all those columns--there's no shorthand in this case. Then change the HAVING clause to COUNT(*) > 10.

回复收藏 0 原文

~没有更多了~