a 表，b表问题

[lsypenghong]

a表中2个字段a1(string),a2（number）
b表中2个字段b1(string),b2(number)

怎么找到表a.sum(a2)等于表b.b2的记录,条件a.a1=b.b1。
a表，b表中的记录数很多，就是批量找。

[lqjava]

看来是楼主在进行数据库设计时有缺陷才造成有这样的需求出现。

[lqjava]

奥看明白了，代码如下：
SELECT a.a1, sum(a.a2)
FROM a, b
where a.a1=b.b1
group by a.a1 having sum(a.a2)=b.b2

[lqjava]

楼主别忘了给俺分阿

[让你望见影子的墙]

又要猜楼主的意思

[7年]

1楼可以

[liangCK]

SELECT *
FROM
(
   SELECT a1,SUM(a2) AS a2
   FROM A
   GROUP BY a1
) AS a
    JOIN B
        ON a.a1=B.b1 AND a.a2=B.b2

[等不到来世]

select * from 表a t
where (select sum(a2) from 表a where a1=t.a1)=(select b2 from 表b where b1=t.a1)

[claro]

过。

[中国风]

select *
from
b
cross apply
(select sum(a2)a2 from a where a1=b.b1)c
where
b.b2=c.a2

[幸运的意外]

select *
from
(select a1,SUM(a2) as a2 from a表 group by a1
) as a
inner join
b表 b
on a.a1=b.b1

[ws_hgo]

/*
a表中2个字段a1(string),a2（number） 
b表中2个字段b1(string),b2(number) 
怎么找到表a.sum(a2)等于表b.b2的记录,条件a.a1=b.b1。
*/


create table #A
(
  a1 nvarchar(20),
  a2 int
)
insert into #A select'AA',10
insert into #A select'AA',20
insert into #A select'BB',30
insert into #A select'CC',40
insert into #A select'DD',30
insert into #A select'DD',10
insert into #A select'EE',23
insert into #A select'EE',34
insert into #A select'FF',19
insert into #A select'GG',10
create table #B
( 
  b1 nvarchar(20),
  b2 int
)
insert into #B select'AA',30
insert into #B select'BB',20
insert into #B select'CC',30
insert into #B select'DD',40
insert into #B select'EE',30
insert into #B select'FF',23
insert into #B select'GG',34
insert into #B select'HH',19
insert into #B select'II',10

select b.b1,b.b2 from #B b join(select a1,sum(a2) a2 from #A group by a1)A on A.a1=b.b1 and A.a2=b.b2

[fcuandy]

恐怕楼主要问的不是大家理解的那样...


很可能又是指要问 a表a2某些记录加起来等于 b2 的组合