想不通这2个表联合查询就查不出，求解

[lyl744455]

一个是family（家庭）,一个是emname（家庭成员）表
结构如下：
，查询家庭成员最多的familyid和家庭成员姓名uname,可能有多个家庭，
郁闷的是这多个家庭怎么查询，求高人指点

[hithink]

我的解答是这样的，使用了子查询，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))

[kingtiy]

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 )

[lixinbill]

这就是oracle版块 楼主不能是把问题提错地方了吧？？？

[lyl744455]

是的，怎么回事的我用sql server不行，oracle的话楼上10楼的没问题，可以查出结果来
我以为不论哪个都一样，唉！谢谢啦，有用sql server能够搞出来的也到这里踩个脚印啊

[luo_riser]

看不到图

[lixinbill]

看不到图 楼主发建表 和 插数据的sql上来吧

[lyl744455]

哦，好的
family表
familyid family phoneno
1 lijia 54343
2 wangjia 5654656
3 zhaojia 6546456
4 caijia 765765
5 ququ 869548605

emname表
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的家庭成员最多的，

[lixinbill]

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)
) 

[lyl744455]

4楼的这位同志我之前也有差不多和你一样的想法，但
max(count(*))有问题啊，报这样的错
消息 130，级别 15，状态 1，第 1 行
不能对包含聚合或子查询的表达式执行聚合函数。

[lixinbill]

我发之前已经确定没错误了。

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)
) 

执行没错误啊

[lixinbill]

1 hghjgj
1 gfgf
1 gfd
4 jukyuy
4 uyjyt
4 juyuu