create table family(familyid number,family varchar2(20),phoneno varchar2(30)) insert into family(familyid ,family ,phoneno ) select 1, 'lijia','54343' from dual union all select 2, 'wangjia','5654656' from dual union all select 3, 'zhaojia','6546456' from dual union all select 4, 'caijia','765765' from dual union all select 5, 'ququ','869548605' from dual commit;create table emname(id number,familyid number,uname varchar2(30)) insert into emname(id ,familyid ,uname) select 1, 1, 'gfd' from dual union all select 2, 1, 'gfgf' from dual union all select 3, 1, 'hghjgj' from dual union all select 4, 2, 'hjyy' from dual union all select 5, 3, 'jhjyh' from dual union all select 6, 4, 'juyuu' from dual union all select 7, 5, 'kytr' from dual union all select 8, 4, 'uyjyt' from dual union all select 9, 4, 'jukyuy' from dual union all select 10, 3, 'jhjhgj' from dual commit;select familyid,uname from emname where familyid in ( select familyid from emname group by familyid having count(*) = (select max(count(*)) from emname group by familyid) )
我发之前已经确定没错误了。select familyid,uname from emname where familyid in ( select familyid from emname group by familyid having count(*) = (select max(count(*)) from emname group by familyid) ) 执行没错误啊
1 hghjgj 1 gfgf 1 gfd 4 jukyuy 4 uyjyt 4 juyuu
我的解答是这样的,使用了子查询,PS:max(count(*))在Oracle中可以使用,但其他DBMS不支持 select familyid,uname from emname where familyid in( select familyid from emname group by familyid having count(*)>=(select count(*) from emname group by familyid))
SELECT familyid , uname FROM emname WHERE familyid = ( SELECT familyid FROM ( SELECT familyid , row_number() OVER ( ORDER BY c DESC ) rk FROM ( SELECT familyid , count(0) c FROM emname GROUP BY familyid ) q ) p WHERE rk = 1 )
family表
familyid family phoneno
1 lijia 54343
2 wangjia 5654656
3 zhaojia 6546456
4 caijia 765765
5 ququ 869548605emname表
id familyid uname
1 1 gfd
2 1 gfgf
3 1 hghjgj
4 2 hjyy
5 3 jhjyh
6 4 juyuu
7 5 kytr
8 4 uyjyt
9 4 jukyuy
10 3 jhjhgj想查出这样的结果,familyid为1的和4的家庭成员最多的,
create table family(familyid number,family varchar2(20),phoneno varchar2(30))
insert into family(familyid ,family ,phoneno )
select 1, 'lijia','54343' from dual
union all
select 2, 'wangjia','5654656' from dual
union all
select 3, 'zhaojia','6546456' from dual
union all
select 4, 'caijia','765765' from dual
union all
select 5, 'ququ','869548605' from dual
commit;create table emname(id number,familyid number,uname varchar2(30))
insert into emname(id ,familyid ,uname)
select 1, 1, 'gfd' from dual
union all
select 2, 1, 'gfgf' from dual
union all
select 3, 1, 'hghjgj' from dual
union all
select 4, 2, 'hjyy' from dual
union all
select 5, 3, 'jhjyh' from dual
union all
select 6, 4, 'juyuu' from dual
union all
select 7, 5, 'kytr' from dual
union all
select 8, 4, 'uyjyt' from dual
union all
select 9, 4, 'jukyuy' from dual
union all
select 10, 3, 'jhjhgj' from dual
commit;select familyid,uname from emname
where familyid in
(
select familyid from emname
group by familyid
having count(*) = (select max(count(*)) from emname
group by familyid)
)
max(count(*))有问题啊,报这样的错
消息 130,级别 15,状态 1,第 1 行
不能对包含聚合或子查询的表达式执行聚合函数。
where familyid in
(
select familyid from emname
group by familyid
having count(*) = (select max(count(*)) from emname
group by familyid)
) 执行没错误啊
1 gfgf
1 gfd
4 jukyuy
4 uyjyt
4 juyuu
select familyid,uname
from emname
where familyid in(
select familyid
from emname
group by familyid
having count(*)>=(select count(*) from emname group by familyid))
uname
FROM emname
WHERE familyid = ( SELECT familyid
FROM ( SELECT familyid ,
row_number() OVER ( ORDER BY c DESC ) rk
FROM ( SELECT familyid ,
count(0) c
FROM emname
GROUP BY familyid ) q ) p
WHERE rk = 1 )
我以为不论哪个都一样,唉!谢谢啦,有用sql server能够搞出来的也到这里踩个脚印啊