select g.* from `group` g inner join (select groupid from `user` group by groupid having count(*)<10) b on g.groupid=b.groupid
== 思想重于技巧 ==
对了,group 表新增一列 num 然后 update group as g set num=(select count(*) from `user` as u where u.groupid=g.groupid ); select * from group where num>10;这样选出来的结果是对的
from `group` g inner join (select groupid from `user` group by groupid having count(*)<10) b
on g.groupid=b.groupid
然后
update group as g set num=(select count(*) from `user` as u where u.groupid=g.groupid );
select * from group where num>10;这样选出来的结果是对的
CREATE TABLE `group` (
`groupid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`groupname` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;
表 `user`:CREATE TABLE `user` (
`userid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 255 ) NOT NULL ,
`groupid` INT( 11 ) NOT NULL
) ENGINE = MYISAM ;
MySQL 版本: 5.1.23-rc-community