现在有一张family表,有如下字段:family_id ,file_no, reside_organ
目前我要查询所有file_no重复的家庭,然后按照reside_organ 排序,要把所有重复的家庭都要列出来
我是这样写的
select * from family a where exists (select 'x' from family b where
a.file_no=b.file_no
and a.family_id <> b.family_id)
order by a.reside_organ然后显示出来的数据,file_no重复的,同一个家庭就显示了好多条,
然后我在family_id 加了distinct 这样问题也出来了,那种只有一条的看起来没有跟他重复的也显示出来了
希望大家看看我写的有什么问题,
或者能再给我提供一种别的思路
目前我要查询所有file_no重复的家庭,然后按照reside_organ 排序,要把所有重复的家庭都要列出来
我是这样写的
select * from family a where exists (select 'x' from family b where
a.file_no=b.file_no
and a.family_id <> b.family_id)
order by a.reside_organ然后显示出来的数据,file_no重复的,同一个家庭就显示了好多条,
然后我在family_id 加了distinct 这样问题也出来了,那种只有一条的看起来没有跟他重复的也显示出来了
希望大家看看我写的有什么问题,
或者能再给我提供一种别的思路
select min(family_id) family_id,file_no,min(reside_organ) reside_organ
from a
group by file_no
order by reside_organ select min(family_id) family_id,file_no,min(reside_organ) reside_organ
from a
where file_no in (select file_no from a group by file_no having count(1) > 1)
group by file_no
order by reside_organ
family_id file_no reside_organ
1 11 11
2 22 22
3 11 11
4 33 33
5 11 11
6 10 11查询之后我需要按照以下的结果显示:
family_id file_no reside_organ
6 10 11
1 11 11
3 11 11
5 11 11
2 22 22
4 33 33
from family a
where exists (select 1 from family
where file_no=a.file_no and family_id != a.family_id)
order by file_no,reside_organ
family_id file_no reside_organ
1 11 11
2 22 22
3 11 11
4 33 33
5 11 11
6 10 11 查询之后我需要按照以下的结果显示:
family_id file_no reside_organ
1 11 11
3 11 11
5 11 11
上面的这部分写错了,查询结果应该是这个,只显示file_no重复的那些数据
from a
where file_no in (select file_no from a group by file_no having count(1) > 1)
group by file_no
order by reside_organ ;
这个方法可行的
from a
where file_no in (select file_no from a group by file_no having count(1) > 1)
group by file_no
order by reside_organ
from a
where file_no in (select file_no from a group by file_no having count(1) > 1)
group by file_no
order by reside_organ