SQL - 在 MySQL 中进行聚合

发布于 2024-10-15 01:39:39 字数 260 浏览 3 评论 0原文

我正在使用 MySQL 并编写此查询：

select gauno, count(potno)
from druide_potion
group by gauno
having count(potno) = min(count(potno))

但是 Mysql 说：“#1111 - 组函数的使用无效”。

这个请求哪里不正确？（当我删除 HAVING 时，我没有出现错误，但也没有达到预期的结果）。

谢谢。

原文

I'm using MySQL and writing this query :

select gauno, count(potno)
from druide_potion
group by gauno
having count(potno) = min(count(potno))

But Mysql says : "#1111 - Invalid use of group function".

In what is this request incorrect? (When I remove the HAVING, I haven't the error but haven't the result expected as well).

Thanks.

分享到QQ

分享到微博

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

发布评论

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

甜中书 2024-10-22 01:39:39

在having 子句中，每个聚合仅返回一个值，因此请求count() 的min() 没有任何意义。

您可能正在寻找类似这样的内容：

select  *
from    druide_potion
group by
        gauno
having  count(potno) = 
        (
        select  count(potno)
        from    druide_potion
        group by
                gauno
        order by
                 count(potno)
        limit 1
        )

这将返回所有具有非空 potno 列且行数最少的 gauno。

In the having clause, each aggregate returns only one value, so requesting the min() of count() makes no sense.

You're probably looking for something like this:

select  *
from    druide_potion
group by
        gauno
having  count(potno) = 
        (
        select  count(potno)
        from    druide_potion
        group by
                gauno
        order by
                 count(potno)
        limit 1
        )

This would return all gauno with the minimum amount of rows with a non-null potno column.

回复收藏 0 原文

生生不灭 2024-10-22 01:39:39

select gauno, count(potno)
  from druide_potion
 group by gauno
 order by count(potno)
 limit 1

select gauno, count(potno)
  from druide_potion
 group by gauno
 order by count(potno)
 limit 1

回复收藏 0 原文

乖乖哒 2024-10-22 01:39:39

正确的 ANSI 查询

SELECT D.*
FROM
(
    select min(cnt) MinCount
    FROM
    (
        select gauno, count(potno) cnt
        from druide_potion
        group by gauno
    )
    Counted1
) MinCounted
inner join
(
    select gauno, count(potno) Cnt
    from druide_potion
    group by gauno
) Counted2
    on MinCounted.MinCount = Counted.Cnt
inner join druide_potion D
    ON D.gauno = Counted2.gauno

Proper ANSI query

SELECT D.*
FROM
(
    select min(cnt) MinCount
    FROM
    (
        select gauno, count(potno) cnt
        from druide_potion
        group by gauno
    )
    Counted1
) MinCounted
inner join
(
    select gauno, count(potno) Cnt
    from druide_potion
    group by gauno
) Counted2
    on MinCounted.MinCount = Counted.Cnt
inner join druide_potion D
    ON D.gauno = Counted2.gauno

回复收藏 0 原文

~没有更多了~