再次请教一个连接查询问题。谢谢大家！

[benbebnmao]

之前发过一贴，可惜问题还是没解决，我把问题做了下简化，导致没表述清楚。
table2原始的数据是：
table1:
id value1
1 100
2 200
3 700

table2：
id value2
1 12
1 33
3 87
3 9
我想得到的结果是：
我想得到的查询结果为
id value1 value2
1 100 45
2 200 0 (或者显示null也行）
3 700 96

我的sql得不到2号记录，我的sql 是：

 
select a.id,a.value1,sum(b.value2) from table1 a left outer join table2 b on a.id=b.id 
 where a.id in ('1','2','3') 
 group by a.id,b.id,a.value1 

怎样写sql才能得到想要的形式，多谢！

[rucypli]

select A.id,A.value1,B.value2
from table1 A left join (select id,sum(value2) as value2 as value2 from table2 group by id) B
on A.id = B.id

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-26 13:03:09
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go 
create table [table1]([id] int,[value1] int)
insert [table1]
select 1,100 union all
select 2,200 union all
select 3,700
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go 
create table [table2]([id] int,[value2] int)
insert [table2]
select 1,12 union all
select 1,33 union all
select 3,87 union all
select 3,9
--------------开始查询--------------------------
select
   isnull(a.id,b.id) as id,
   isnull(a.value1,0),isnull(b.value2,0)
from
   (select id,sum(value1) as value1 from table1 group by id) a
full join
   (select id,sum(value2) as value2 from table2 group by id)b
on
   a.id=b.id
----------------结果----------------------------
/* id                      
----------- ----------- -----------
1           100         45
2           200         0
3           700         96

(3 行受影响)

*/

[geniuswjt]

--如果table1为基表，且id是全的，那么没必要用full join，试试下面的
select a.*,b.value2 from table1 a left join (
select id,sum(value2) value2 from table2
group by id
) b on (a.id=b.id)
where a.id in ('1','2','3')

[benbebnmao]

谢谢fredrickhu、rucypli！这次没问题了。结贴

[jmx123456789]

[Quote=引用 1 楼 fredrickhu 的回复:]
SQL code

select
isnull(a.id,b.id) as id,
isnull(a.value1,0),isnull(b.value2,0)
from
(select id,sum(value1) as value1 from table1 group by id) a
full join
(select id,sum(value2) as……
[/Quote]
+1

[--小F--]

select
   isnull(a.id,b.id) as id,
   isnull(a.value1,0),isnull(b.value2,0)
from
   (select id,sum(value1) as value1 from table1 group by id) a
full join
   (select id,sum(value2) as value2 from table2 group by id)b
on
   a.id=b.id