MySql:WHERE NOT EXISTS(跳过重复项)，分组依据为

Question

我有这个挑战，我喜欢一种解决方案，但我不明白为什么它是错误的。

编写一个查询，打印每个学生创建的hacker_id、姓名和挑战总数。按挑战总数降序排列结果。如果有多个学生创建了相同数量的挑战，则按hacker_id对结果进行排序。如果有多个学生创建了相同数量的挑战，并且计数小于创建的最大挑战数量，则将这些学生排除在结果之外。

黑客：hacker_id是黑客的id，name是黑客的名字。挑战：challenge_id是挑战的id，hacker_id是创建挑战的学生的id。

我的解决方案：

select h.hacker_id, h.name, count(c.challenge_id) as total from challenges c
join hackers h
on h.hacker_id= c.hacker_id

where not exists
(select h1.hacker_id, h1.name, count(C1.challenge_id) as total1 from challenges C1
join hackers h1 on h1.hacker_id= c1.hacker_id
group by h1.hacker_id, h1.name
having total1 < max(count(c.challenge_id)  and  total1 = count(c.challenge_id)
order by total1 desc)

group by h.hacker_id, h.name
order by total desc

我收到以下错误：第1行出现错误1064(42000(：SQL语法有错误；查看与MySQL服务器版本相对应的手册，了解使用near‘order by total1 desc(的正确语法

按h.hacker_id、h.name分组在第16行按总desc排序

Answer 1

select h.hacker_id, h.name, count(c.challenge_id) as total from challenges c
join hackers h
on h.hacker_id= c.hacker_id

where not exists
(select t.total1

from (select  count(C1.challenge_id) as total1 
from challenges C1
join hackers h1 on h1.hacker_id = c1.hacker_id
group by h1.hacker_id) t
having t.total1 < big = (select max(count) 
from (select count(c3.challenge_id) as count from challenges c3 group by c3.hacker_id))) big
and  t.total1 = 1
)

group by h.hacker_id, h.name
order by total desc