一个sql查询咋写啊？

[马老虎]

表A↓
---------------------
name count1
北京 3
天津 4

表B↓
---------------------
name count2
北京 5
山东 6

表C↓
----------------------
name count3
河北 7

查询结果↓
---------------------------------------------
name count1 count2 count3
北京 3 5 0
天津 4 0 0
河北 0 0 7
山东 0 6 0

--这个sql该咋写呢？

[水族杰纶]

select name ,
       sum(count1)count1,
       sum(count2)count2,
       sum(count3)count3
from(
select name,count1,0  count2,0 count3 from ta
union all
select name,0 count1, count2,0 count3 from ta
union all
select name,0 count1, 0 count2,count3 from tc
)t 
group by name

[jylijie]

asdasdasdadsasd

[--小F--]

没关联？

[水族杰纶]

--> Title  : Generating test data [tA]
--> Author : 
--> Date   : 2009-12-04 13:46:52
if object_id('[tA]') is not null drop table [tA]
go
create table [tA] (name nvarchar(4),count1 int)
insert into [tA]
select N'北京',3 union all
select N'天津',4
if object_id('[tB]') is not null drop table [tB]
go
create table [tB] (name nvarchar(4),count2 int)
insert into [tB]
select N'北京',5 union all
select N'山东',6
if object_id('[tC]') is not null drop table [tC]
go
create table [tc] (name nvarchar(4),count3 int)
insert into [tC]
select N'河北',7

select name ,
       sum(count1)count1,
       sum(count2)count2,
       sum(count3)count3
from(
select name,count1,0  count2,0 count3 from ta
union all
select name,0 count1, count2,0 count3 from tb
union all
select name,0 count1, 0 count2,count3 from tc
)t 
group by name
/*
name count1      count2      count3
---- ----------- ----------- -----------
山东   0           6           0
天津   4           0           0
北京   3           5           0
河北   0           0           7

(4 個資料列受到影響)
*/

[--小F--]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codeselect name ,sum(count1)count1,sum(count2)count2,sum(count3)count3from(select name,count1,0 count2,0 count3from taunionallselect name,0 count1, count2,0 count3from taunionallselect name,0 cou?-
[/Quote]

UP

[华夏小卒]

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-04 13:51:30
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @a
declare @a table (name varchar(4),count1 int)
insert into @a
select '北京',3 union all
select '天津',4
---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-04 13:51:30
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @b
declare @b table (name varchar(4),count2 int)
insert into @b
select '北京',5 union all
select '山东',6
---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-04 13:51:30
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @c
declare @c table (name varchar(4),count3 int)
insert into @c
select '河北',7

select 
name=isnull(isnull(a.name,b.name),c.name),
count1=isnull(a.count1,0),
count2=isnull(b.count2,0),
count3=isnull(c.count3,0)
from @a a
full join @b b on a.name=b.name
full join @c c on a.name=c.name 


name count1      count2      count3
---- ----------- ----------- -----------
北京   3           5           0
天津   4           0           0
山东   0           6           0
河北   0           0           7

(4 行受影响)

[马老虎]

上面的高手让我很佩服！
小弟谢谢！！