一条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
-------------------
13000
20400
30040
否则第三行的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
On A.id = B.id
Left Join
tab3 C
On A.id = C.id
Left Join
tab4 D
On A.id = D.id
--如果id不能確定，可以如下
Select id, SUM(t1) As t1, SUM(t2) As t2, SUM(t3) As t3, SUM(t4) As t4
From
(Select id, t1, 0 As t2, 0 As t3, 0 As t4 From tab1
Union All
Select id, 0, t2, 0, 0 From tab2
Union All
Select id, 0, 0, t3, 0 From tab3
Union All
Select id, 0, 0, 0, t4 From tab4
) A
Group By
id
