22,300
社区成员




/*
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
select * from 表a t
where (select sum(a2) from 表a where a1=t.a1)=(select b2 from 表b where b1=t.a1)
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