mysql里有一张表my_Q,有asid,uid两个字段,分别表示组id,组中成员id,现在要取组成员数最多的组id和组中成员数,表中没有‘组成员数’这个字段,除了用下面语句查询外还有没有其他的方法?
select asid,count(uid) from my_Q group by asid order by count(uid) desc limit 1
select asid,count(uid) from my_Q group by asid order by count(uid) desc limit 1
会出错啊 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
SELECT d1,count(*) as dd from aa1 group by d1) a
left join
(
SELECT d1,count(*) as dd from aa1 group by d1) b
on a.dd<=b.dd
group by a.d1 having count(b.d1)=1
实际上你的语句应该是最精简的了d1:asid
d2:uid
from (select asid,count(uid) from my_Q group by asid) t
order by 2 desc
limit 1
select a.d1,max(a.dd) from (
SELECT d1,count(*) as dd from aa1 group by d1) a
group by a.d1 having max(a.dd)=(select max(dd) from (SELECT d1,count(*) as dd from aa1 group by d1))在MYSQL中这种要求最好用LIMIT处理