表一
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 语句?
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 语句?
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)
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
where x.a=b.a and x.b<>sum(y.b)
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', 2select 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