select t.* from a t where exists(select 1 from (select xm,sfzhm from a group by xm, sfzhm having count(1) >= 2) m where m.xm = t.xm and m.sfzhm = t.sfzhm) order by t.xm , t.sfzhm
这个可以用自连接吧select * from table_name a,table_name b where a.xm = b.sfzhm
谢谢2楼,我要的就是这种结果,由于我刚学oracle,因此还请解释下这条语句, select 1 from (select xm,sfzhm from a group by xm, sfzhm having count(1) >= 2) m where m.xm = t.xm and m.sfzhm = t.sfzhm,这条语句有点复杂,请解释下,谢谢。 另外能否使查询速度更快点?
select1from (select xm,sfzhmfrom agroupby xm, sfzhmhavingcount(1)>=2) mwhere m.xm= t.xmand m.sfzhm= t.sfzhm)orderby t.xm , t.sfzhm --这个返回的布尔值select xm,sfzhm from a group by xm, sfzhm having count(1) >= 2--按 xm, sfzhm 分组,大于等于2 的就返回 where m.xm = t.xm and m.sfzhm = t.sfzhm--匹配条件 应该可以看懂了吧
select t.* from a t where exists(select 1 from
(select xm,sfzhm from a group by xm, sfzhm having count(1) >= 2) m
where m.xm = t.xm and m.sfzhm = t.sfzhm)
order by t.xm , t.sfzhm
from table_name a,table_name b
where a.xm = b.sfzhm
谢谢2楼,我要的就是这种结果,由于我刚学oracle,因此还请解释下这条语句,
select 1 from
(select xm,sfzhm from a group by xm, sfzhm having count(1) >= 2) m
where m.xm = t.xm and m.sfzhm = t.sfzhm,这条语句有点复杂,请解释下,谢谢。
另外能否使查询速度更快点?
select1from
(select xm,sfzhmfrom agroupby xm, sfzhmhavingcount(1)>=2) mwhere m.xm= t.xmand m.sfzhm= t.sfzhm)orderby t.xm , t.sfzhm --这个返回的布尔值select xm,sfzhm from a group by xm, sfzhm having count(1) >= 2--按 xm, sfzhm 分组,大于等于2 的就返回 where m.xm = t.xm and m.sfzhm = t.sfzhm--匹配条件 应该可以看懂了吧