求一SQL查询语句！

[cimzzxiang]

表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

[cimzzxiang]

楼上不对啊，我需要的是B中没出现A某个id的情况下，显示该id出现的次数为0

[Oraclefans_]

[Quote=引用 3 楼 cimzzxiang 的回复:]
楼上不对啊，我需要的是B中没出现A某个id的情况下，显示该id出现的次数为0
[/Quote]
我的不行吗？莫可能呀。。

[cimzzxiang]

不行啊

[Oraclefans_]

[Quote=引用 5 楼 cimzzxiang 的回复:]
不行啊
[/Quote]
怎么个不行，执行的结果是什么呢？

[Oraclefans_]

select id a_id,count(*) from a,b where a.id=b.a_id(+) group by a.id
这样总行了吧。。。

[cimzzxiang]

好吧，只能显示
a_id count(a_id)
1 2
2 2

而 3 0 没有

[Oraclefans_]

[Quote=引用 7 楼 oraclefans_ 的回复:]
select id a_id,count(*) from a,b where a.id=b.a_id(+) group by a.id
这样总行了吧。。。
[/Quote]
这个的结果也一样吗？

[cimzzxiang]

朋友 你可以自己试试先

[gelyon]

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(+)

[yu_xiao_chen]

select a.id ,count(*) from a,b where a.id=b.a_id group by a.id

[thivy123]

[Quote=引用 18 楼 i4xiang 的回复:]
建议用左连接

select a.id,count(b.a_id)
from a left join b on a.id=b.a_id
group by a.id
[/Quote]
赞成，用左连接即可。

[心中的彩虹]

[Quote=引用楼主 cimzzxiang 的回复:]
表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

……
[/Quote]

---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

[Oraclefans_]

[Quote=引用 10 楼 cimzzxiang 的回复:]

朋友 你可以自己试试先
[/Quote]
-------兰州，我这里的运行结果。。。。

SQL> with a as
  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           0

SQL> 

[fxianxian]

SQL>   with a as (select 1 id, 'zhangsan' name from dual
  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          0

SQL> 

[yyq02271982]

select a_id,count(*) from B where exists(select a_id from A where B.a_id = A.a_id)

[yyq02271982]

select a_id,(select count(*) from B where B.a_id = A.a_id) from A

[i4xiang]

建议用左连接

select a.id,count(b.a_id)
from a left join b on a.id=b.a_id
group by a.id

[tangren]

  SELECT A.id, COUNT(B.a_id)
    FROM A, B
   WHERE A.id = B.a_id(+)
   GROUP BY A.id;

[悟道先生]

select a.id,count(b.a_id) from a left join b on a.id=b.a_id group by a.id;

[tiigger]

外连接