Çë½ÌÒ»Ìõ¿ìËÙ׼ȷµÄoracle²éѯÓï¾ä

ÔÚÒ»ÕűíaÖУ¬ÓÐ×Ö¶Îxm,sfzhm,rid,xb,mzµÈ6¸ö×ֶΣ¬Èç¹ûxmºÍsfzhmÏàͬ£¬ÄÇô¾Í˵ÕâÁ½Ìõ¼Ç¼Ïàͬ£¬Çë½ÌÔõô½«ÕâÖÖÇé¿öϵÄÊý¾Ý²éѯ³öÀ´¡£

---

SQL code:

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

---

ÒýÓÃ

SQL codeselect t.*from a twhereexists(select1from
(select xm,sfzhmfrom agroupby xm, sfzhmhavingcount(1)>=2) mwhere m.xm= t.xmand m.sfzhm= t.sfzhm)orderby t.xm , t.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£¬ÕâÌõÓï¾äÓе㸴ÔÓ£¬Çë½âÊÍÏ£¬Ð»Ð»¡£
ÁíÍâÄÜ·ñʹ²éѯËٶȸü¿ìµã£¿

---

ÒýÓÃ

SQL codeselect t.*from a twhereexists(select1from
(select xm,sfzhmfrom agroupby xm, sfzhmhavingcount(1)>=2) mwhere m.xm= t.xmand m.sfzhm= t.sfzhm)orderby t.xm , t.sfzhm

select1from
(select xm,sfzhmfrom agroupby xm, sfzhmhavingcount(1)>=2) mwhere m.xm= t.xmand m.sfzh