if OBJECT_ID('a') is not null
drop table a
create table a(
id int primary key identity(1,1),
name nvarchar(20),
pname nvarchar(20)
)
insert a(name,pname)
select 'ssss','ssss' union all
select 'aaaa','aaaa' union all
select 'dddd','dddd' union all
select 'eeee','eeee' if OBJECT_ID('b') is not null
drop table b
create table b(
id int primary key identity(1,1),
aid int,
qian int,
shu int
)
insert b(aid,qian,shu)
select 1,2,3 union all
select 1,2,3 union all
select 2,2,3 union all
select 2,2,3 union all
select 3,2,3 union all
select 3,3,3 union all
select 3,3,3 union all
select 4,2,3 union all
select 4,2,3 我想查出的结果是
aid qian shu
1 4 6
2 4 6
3 8 9
4 4 6
drop table a
create table a(
id int primary key identity(1,1),
name nvarchar(20),
pname nvarchar(20)
)
insert a(name,pname)
select 'ssss','ssss' union all
select 'aaaa','aaaa' union all
select 'dddd','dddd' union all
select 'eeee','eeee' if OBJECT_ID('b') is not null
drop table b
create table b(
id int primary key identity(1,1),
aid int,
qian int,
shu int
)
insert b(aid,qian,shu)
select 1,2,3 union all
select 1,2,3 union all
select 2,2,3 union all
select 2,2,3 union all
select 3,2,3 union all
select 3,3,3 union all
select 3,3,3 union all
select 4,2,3 union all
select 4,2,3 我想查出的结果是
aid qian shu
1 4 6
2 4 6
3 8 9
4 4 6
sum(isnull(b.qian,0)) as qian,
sum(isnull(b.shu,0)) as shu
from a
left join b
on a.id=b.aid
group by a.id
SELECT
a.id AS aid
,SUM(ISNULL(b.qian,0)) AS qian
,SUM(ISNULL(b.shu,0)) AS shu
FROM a WITH(NOLOCK)
LEFT JOIN b
ON b.aid = a.id
GROUP BY a.id