oracleÏÂÕâÑùµÄ²éѯÔõôŪ£¡

table1
number  name
511    ±±¾©
512    ÉϺ£
513    ¹ãÖÝ
514    ÉîÛÚ
611    ³É¶¼
612    ÖØÇì
...

table2£¨Õâ¸öÊÇÄÚÈÝ±í£¬number¶ÔÓ¦table1µÄnumber£©
number  ....
511   
512
512


²éѯ½á¹û
number    name    count
511      ±±¾©    1
512      ÉϺ£    2
513      ¹ãÖÝ    0
514      ÉîÛÚ    0

£¨³É¶¼ºÍÖØÇ춼²»Òª£¬ÒòΪËûÃÇÊÇ¡®61¡¯¿ªÍ·µÄ£©
1.Èç¹û¿ÉÒÔ£¬Çë¸øÏÂsql´úÂë

---

select t1.name,t1.number, sum(nvl(t2.number, 0)) count from t1, t2 where t1.number=t2.number(+) and t1.number not like '61%' group by t1.name,t1.number

---

select t1.name,t1.number, sum(nvl(t2.number, 0)) count from t1, t2 where t1.number=t2.number(+) and t1.number not like '61%' group by t1.name,t1.number
Õâ¸öЧÂÊÔõôÑù°¡£¿
ÒòΪtable2£¨ÄÚÈÝ±í£©ÖÐÊý¾ÝÁ¿¿ÉÄÜ»áÓеã¶à(200w×óÓҵıê×¼)

---

SQL code:

select distinct t1.number,t1.name,count(t2.number) over(partition by t1.name)
from table1 t1,table2 t2
where t1.number = t2.number