表A,表B
A(id,name) B(id,a_id,year)
例:A id name
1 张三
2 李四
3 王五
B id a_id year
1 1 2009
2 1 2010
3 2 2009
4 2 2010
要求,查询B表中出现A的次数:
a_id count(a_id)
1 2
2 2
3 0
A(id,name) B(id,a_id,year)
例:A id name
1 张三
2 李四
3 王五
B id a_id year
1 1 2009
2 1 2010
3 2 2009
4 2 2010
要求,查询B表中出现A的次数:
a_id count(a_id)
1 2
2 2
3 0
select a_id, count(a_id)
from b,a
where b.a_id =a.id
group by b.a_id;
这样总行了吧
a_id count(a_id)
1 2
2 2而 3 0 没有
select a.id,nvl(b.n,0) 人数
from a,(select a_id,count(*) n from b group by a_id ) b
where a.id=b.a_id(+)
FROM A, B
WHERE A.id = B.a_id(+)
GROUP BY A.id;
---A(id,name) B(id,a_id,year)select a.id,count(b.a_id)
from a left join b on a.id=b.a_id
group by a.id
2 (
3 select 1 id ,'张三' name from dual union all
4 select 2 id ,'李四' name from dual union all
5 select 3 id ,'王五' name from dual
6 ),b as
7 (
8 select 1 id,1 a_id,'2009' year from dual union all
9 select 2 id,1 a_id,'2010' year from dual union all
10 select 3 id,2 a_id,'2009' year from dual union all
11 select 4 id,2 a_id,'2010' year from dual
12 )
13 select a.id a_id, count(a_id)
14 from a, b
15 where a.id = b.a_id(+)
16 group by a.id
17 / A_ID COUNT(A_ID)
---------- -----------
1 2
2 2
3 0SQL>
2 union
3 select 2 id, 'lisi' name from dual
4 union
5 select 3 id, 'wangwu' name from dual
6 ),
7 b as (select 1 id, 1 a_id, 2009 year from dual
8 union
9 select 2 id, 1 a_id, 2010 year from dual
10 union
11 select 3 id, 2 a_id, 2009 year from dual
12 union
13 select 4 id, 2 a_id, 2010 year from dual
14 )
15 select a.id, nvl(b.count_id, 0) count_id
16 from a, (select a_id, count(a_id) count_id from b group by a_id) b
17 where a.id = b.a_id(+)
18 / ID COUNT_ID
---------- ----------
1 2
2 2
3 0SQL>
from a left join b on a.id=b.a_id
group by a.id