问一个sql语句实现
表一
create   table   t_one(   a   char(1),   b   number(2)   );
表二
create   table   t_two(   a   char(1),   b   number(2)   );

表一数据:
a1     1
a2     2  
a3     3
a4     4

表二数据:
a1     1
a2     1
a2     1
a3     1
a3     2
a4     1
a4     2


想select   表一和表二,select   出的结果数据如下:
a4     3    


因为表二a1行的对应b=1,   a2行的   count(b)=2,a3行的count(b)=3     这些的b总值都与
表一对应a行的值相等，只是剩下表二的a4行的count(b) <表一a4   对应b值,所以列出a4   3


请问上面问题怎么写select   语句？


------解决方案--------------------
是sum不是count吧？
------解决方案--------------------

select * from (select a,sum(b) as b from t_two
group by a)a
where not exists(select * from t_one where b=a.b)
------解决方案--------------------
--this?

select T2.*
from
(select a,count(b) as b from 表二 group by a) T2
inner join
表一 T1
on T2.a=T1.a and T2.b <> T1.b
------解决方案--------------------
select y.a,sum(y.b) from t_one x,t_two y
where x.a=b.a and x.b <> sum(y.b)
------解决方案--------------------
呃，少了group by y.a
------解决方案--------------------
--表一
create table t_one
( a char(2),b int )
--表二
create table t_two( a char(2), b int )

--表一数据:
insert t_one
select 'a1 ', 1
union all
select 'a2 ', 2
union all
select 'a3 ', 3
union all
select 'a4 ', 4

--表二数据:
insert t_two
select 'a1 ', 1
union all
select 'a2 ', 1
union all
select 'a2 ', 1
union all
select 'a3 ', 1
union all
select 'a3 ', 2
union all
select 'a4 ', 1
union all
select 'a4 ', 2


select t.a,sum(t.b)
from t_two t, t_one t1
where
t.a = t1.a and t1.b <> (select sum(b) from t_two where a = t.a group by a)
group by t.a