表A
aid numa
1 2
2 3
3 12
4 12表B
bid aid numb
1 2 23
2 2 21
3 3 4
4 4 21select sum(numb) as numb from 表a inner join 表b on 表a.aid = 表b.bid where 表a.aid in (2,3)查出来是
numb
48怎么查询出
numb numa
48 15就是把主表的一个值也相加。
aid numa
1 2
2 3
3 12
4 12表B
bid aid numb
1 2 23
2 2 21
3 3 4
4 4 21select sum(numb) as numb from 表a inner join 表b on 表a.aid = 表b.bid where 表a.aid in (2,3)查出来是
numb
48怎么查询出
numb numa
48 15就是把主表的一个值也相加。
但这样,numa可能不正确。select numa=(select sum(x.numa) from 表a as x where x.aid=a.aid),
sum(b.numb) as numb
from 表a as a,表b as b where a.aid=b.bid and a.aid in (2,3)
group by a.aid这样肯定行:结合了子查询。
create table 表a(aid int,numa int)
create table 表b(bid int,aid int,numb int)
insert into 表a(aid,numa)
select 1 , 2 union all
select 2 , 3 union all
select 3 , 12 union all
select 4 , 12 insert into 表b(bid,aid,numb)
select 1 , 2 , 23 union all
select 2 , 2 , 21 union all
select 3 , 3 , 4 union all
select 4 , 4 , 21select sum(numb) as numb,numa=(select sum(numa) from 表a where aid in(2,3))
from 表a a
inner join 表b b on a.aid = b.aid
where a.aid in (2,3)
numb numa
----------- -----------
48 15(所影响的行数为 1 行)
把条件写两个地方
select sum(numa) numa,sum(numb) numb from(
select sum(numb) as numb,numa=(select sum(numa) from #表a c where c.aid=a.aid)
from #表a a
inner join #表b b on a.aid = b.aid
where a.aid in (2,3)
group by a.aid
) a
from
ta
inner join
(select aid, sum(numb) as numb from tb group by aid) as tb
on ta.aid = tb.aid
where ta.aid in (2,3)
create table #表A
(aid int, numa int)
insert #表A
select 1 , 2 union all
select 2 , 3 union all
select 3 , 12 union all
select 4 , 12create table #表B
(bid int, aid int,numb int)
insert #表B
select 1 , 2 , 23 union all
select 2 , 2 , 21 union all
select 3 , 3 , 4 union all
select 4 , 4 , 21select (select sum(numa) from #表A where aid in(2,3))as numa,
(select sum(numb) from #表b where aid in(2,3))as numb
select sum(numa) as numa,sum(numb) as numb from 表a inner join 表b on 表a.aid = 表b.bid where 表a.aid in (2,3)
--try
select
sum(numa) as numa,sum(numb) as numb
from
表a inner join 表b
on
表a.aid = 表b.bid
where
表a.aid in (2,3)