一条sql语句能得到这样的结果格式吗？有点难。
四个表结构一样，字段都是整型数字
表一tab1  

id   t1
1     10
2     20
表二tab2
id   t2
1     30

表三tab3
id   t3
2     40

表四tab4没记录
id   t4


希望得到下面格式的结果，没有数值的记为0，一条语句写得出来吗？
id   t1     t2     t3     t4
1     10     30       0       0    
2     20     0       40       0      

多谢各位高手了！



------解决方案--------------------
declare @t1 table (id int, t1 int)
insert @t1
select 1, 10 union all
select 2, 20
declare @t2 table(id int, t2 int)
insert @t2 select 1, 30

declare @t3 table (id int, t3 int)
insert @t3 select 2, 40

declare @t4 table (id int, t4 int)

select a.*, t2 = isnull(b.t2,0), t3 = isnull(c.t3,0), t4 = isnull(d.t4,0) from
@t1 a left join @t2 b on a.id = b.id left join
@t3 c on a.id = c.id left join
@t4 d on a.id = d.id

------解决方案--------------------
Select A.id,
A.t1,
IsNull(B.t2,0) As t2,
IsNull(C.t3,0) As t3,
IsNull(D.t4,0) As t4
From
tab1 A
Left Join
tab2 B
On A.id=B.id
Left Join
tab3 C
On A.id=C.id
Left Join
tab4 D
On A.id=D.id
------解决方案--------------------
Limpire(昨夜小楼)

-- 如果不知道哪个表记录多，用 full join

------------

你這裡會有問題的，你測試下這個

declare @t1 table (id int, t1 int)
insert @t1
select 1, 10

declare @t2 table(id int, t2 int)
insert @t2 select 1, 30

declare @t3 table (id int, t3 int)
insert @t3 select 2, 40

declare @t4 table (id int, t4 int)

-- 如果不知道哪个表记录多，用 full join
select a.*, t2 = isnull(b.t2,0), t3 = isnull(c.t3,0), t4 = isnull(d.t4,0) from
@t1 a full join @t2 b on a.id = b.id full join
@t3 c on a.id = c.id full join
@t4 d on a.id = d.id


------解决方案--------------------
最正确的写法
declare @t1 table (id int, t1 int)
insert @t1
select 1, 10 union all
select 2, 20

declare @t2 table(id int, t2 int)
insert @t2 select 1, 30

declare @t3 table (id int, t3 int)
insert @t3 select 2, 40

declare @t4 table (id int, t4 int)
insert @t4 select 3, 40 --加一句

-- 如果不知道哪个表记录多，用 full join
select id=case when a.id is null then (case when b.id is null then (case when c.id is null then d.id else c.id end)
else b.id end)
else a.id end
,t2 = isnull(b.t2,0), t3 = isnull(c.t3,0), t4 = isnull(d.t4,0)
from @t1 a
full join @t2 b on a.id = b.id
full join @t3 c on a.id = c.id
full join @t4 d on a.id = d.id


-------------------
1 30 0 0
2 0 40 0
3 0 0 40

否则第三行的id 为null
------解决方案--------------------
所以，比較保險的是這個

paoluo(一天到晚游泳的鱼(學習.NET中)) ( ) 信誉：100 2007-08-25 17:09:52 得分: 0


--如果id都在t1中，使用Left Join即可
Select
A.id,
A.t1,
IsNull(B.t2, 0) As t2,
IsNull(C.t3, 0) As t3,
IsNull(D.t4, 0) As t4
From
tab1 A
Left Join
tab2 B