多表统计

[狼王_]

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

[liangCK]

跟多表有关系吗？

[liangCK]

select a.id as aid,
   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

[狼王_]

[Quote=引用 2 楼 liangck 的回复:]
SQL code
select a.id as aid,
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
[/Quote]
为什么我想不到这种方法，你是怎么达到今天的水平的。
授人以渔胜过授人以鱼。。
还望您不吝赐教

[da21]

[Quote=引用 3 楼 zaocha321 的回复:]
引用 2 楼 liangck 的回复:
SQL code
select a.id as aid,
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

为什么我想不到这种方法，你是怎么达到今天的水平的。
授人以渔胜过授……
[/Quote]

楼主，我晕系纸咯！

[狼王_]

[Quote=引用 4 楼 da21 的回复:]
引用 3 楼 zaocha321 的回复:
引用 2 楼 liangck 的回复:
SQL code
select a.id as aid,
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

为什么我想不到这种方法……
[/Quote]
你丫的哪个村里出来的？老子问个问题管你鸟事？？？？我有的是可用分，我就喜欢问这种问题。。。。
你看着不爽不要回老子的帖子啊！！！ 还不是你自己手贱！！！ 我靠( ‵o′)凸

[feixianxxx]

[Quote=引用 5 楼 zaocha321 的回复:]

引用 4 楼 da21 的回复:
引用 3 楼 zaocha321 的回复:
引用 2 楼 liangck 的回复:
SQL code
select a.id as aid,
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 ……
[/Quote]
..淡定

[yuchong1984]

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