写sql查询查出以下效果
------解决方案--------------------
SQL> select * from t1;
ID YJLX YJCONTENT CREATE_DAT YJSOURCE
---------- ---- ---------- ---------- ----------
1 good 123 2012-04-08 1
2 mid 123 2012-04-08 2
3 bad 123 2012-04-08 3
SQL> select * from t2;
ID PID CUS_ CREATE_DAT
---------- ---------- ---- ----------
1 1 z1 2012-04-08
1 2 z2 2012-04-08
2 3 z3 2012-04-08
2 4 z4 2012-04-08
2 5 z5 2012-04-08
SQL> select t1.id,t1.yjlx,nvl(t.count,0),t1.create_date,t1.yjsource from (select id,count(id) count from t2 group by id) t right join t1 on t.id = t1.id;
ID YJLX NVL(T.COUNT,0) CREATE_DAT YJSOURCE
---------- ---- -------------- ---------- ----------
1 good 2 2012-04-08 1
2 mid 3 2012-04-08 2
3 bad 0 2012-04-08 3
SQL>
------解决方案--------------------
select a.id,a.yjlx,b.cun,a.Create_date,a.yjsource
from a,(select id,count(id) as cun from b group by id)c
where a.id=c.id(+);