对于以下 sql 命令
select h.hacker_id
,h.name
,challenges_created
from hackers h
inner join (
select hacker_id
,count(*) as challenges_created /* line 1 */
from challenges
group by hacker_id
order by 2 desc
) tmp on h.hacker_id = tmp.hacker_id
order by challenges_created desc
,h.hacker_id;
到目前为止一切顺利,但是
一旦我尝试将max(count(*)) as maximum
添加到第 1 行,它就会给出错误:
不是单组组函数
这是它给出错误的代码:
select h.hacker_id
,h.name
,challenges_created
from hackers h
inner join (
select hacker_id
,count(*) as challenges_created
,max(count(*)) as maximum
from challenges
group by hacker_id
) tmp on h.hacker_id = tmp.hacker_id
order by challenges_created desc
,h.hacker_id;
我基本上对获得最大计数感兴趣,即到目前为止的最大challenges created
数。
我是sql的新手,请帮助和义务。提前谢谢。是的,当然!我知道最近有很多这样的问题被问过,但没有一个符合我的情况,这就是我再次问它的原因。
试试这个:
with x as (
select h.hacker_id, h.name, count(*) challenges_created
from hackers h
inner join challenges on h.hacker_id = challenges.hacker_id
group by h.hacker_id, h.name
)
select x.*,
(select max(challenges_created) from x) as "max"
from x
order by challenges_created desc, hacker_id;