查询最大值问题

[huwj1986]

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

[nianzhang747]

select t.asid,max(t.c) from (select asid,count(uid) c from my_Q  group by asid) t;

[huwj1986]

[Quote=引用 1 楼 nianzhang747 的回复:]
SQL codeselect t.asid,max(t.c)from (select asid,count(uid) cfrom my_Qgroupby asid) t;
[/Quote]

会出错啊 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[huwj1986]

我想要的结果就像上面那样 从select asid,count(uid) from my_Q group by asid 查得的结果中查询count（uid）的最大值，就是不知道该怎么嵌套，一楼的会出错

[WWWWA]

select a.d1,max(a.dd) from (
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

[ACMAIN_CHM]

select *
from (select asid,count(uid) from my_Q  group by asid) t
order by 2 desc
limit 1

[wwwwb]

or
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处理

[nianzhang747]

嗯 知道了 知识要你测试下

发现没有更简单的语句了

[zhuzhangliang]

知识来源于实践吗