select count(*),a.dwg_id,b.cmpnt_name from a,b where a.dwg_id=b.dwg_id group by a.dwg_id,b.cmpnt_name 行不行?
with TableA as ( select 1 rev_id,11 dwg_id from dual union all select 2 rev_id,11 dwg_id from dual union all select 1 rev_id,22 dwg_id from dual union all select 1 rev_id,33 dwg_id from dual ),TableB as ( select 11 dwg_id,'aa' cmpnt_name from dual union all select 22 dwg_id,'bb' cmpnt_name from dual union all select 33 dwg_id,'cc' cmpnt_name from dual )select b.dwg_id,b.cmpnt_name,count(b.dwg_id) c_id from TableB b left join TableA a on a.dwg_id = b.dwg_id group by b.dwg_id,b.cmpnt_name order by b.dwg_id dwg_id cmpnt_name c_id ----------------------------------- 1 11 aa 2 2 22 bb 1 3 33 cc 1
with TableA as
(
select 1 rev_id,11 dwg_id from dual union all
select 2 rev_id,11 dwg_id from dual union all
select 1 rev_id,22 dwg_id from dual union all
select 1 rev_id,33 dwg_id from dual
),TableB as
(
select 11 dwg_id,'aa' cmpnt_name from dual union all
select 22 dwg_id,'bb' cmpnt_name from dual union all
select 33 dwg_id,'cc' cmpnt_name from dual
)select b.dwg_id,b.cmpnt_name,count(b.dwg_id) c_id
from TableB b left join TableA a on a.dwg_id = b.dwg_id
group by b.dwg_id,b.cmpnt_name
order by b.dwg_id dwg_id cmpnt_name c_id
-----------------------------------
1 11 aa 2
2 22 bb 1
3 33 cc 1