表1ID Value1 a
2 b表2ID Value
1 a
2 b
3 c
表3ID Value
1 a
2 b
表四
ID Value
1 a
2 b
3 c
4 d效果:
ID Value1 Value2 Value3 Value4
1 a a a a
2 b b b b
3 null c null c
4 null null null d
2 b表2ID Value
1 a
2 b
3 c
表3ID Value
1 a
2 b
表四
ID Value
1 a
2 b
3 c
4 d效果:
ID Value1 Value2 Value3 Value4
1 a a a a
2 b b b b
3 null c null c
4 null null null d
from 表1 as a,表2 as b,表3 as b,表4 as d
where a.id=b.id=c.id=d.id
A.VALUE AS Value1 ,
B.Value AS Value2,
C.Value AS Value3,
D.Value AS Value4
FROM D LEFT JOIN A ON D.ID=A.ID
LEFT JOIN C ON D.ID=C.ID
LEFT JOIN B ON D.ID=B.ID
(select isnull(t1.id , t2.id) id , t1.Value1 , t1.Value2 , t2.value value3 from
(select isnull(tb1.id,tb2.id) id , tb1.value value1 , tb2.value value2 from tb1 full join tb2 on tb1.id = tb2.id) t1
full join tb3 t2 on t1.id = t2.id) m1 full join tb4 m2 on m1.id = m2.id
a.id,
a.value as value1,
b.value as value2,
c.value as value3,
d.value as value4
from
表1 as a,
表2 as b,
表3 as b,
表4 as d
where
a.id=b.id=c.id=d.id
from a right join b
on a.id =b.id
right join c
on a.id =c.id
right join d
on a.id =d.id
create table tb2(ID int , Value varchar(10))
create table tb3(ID int , Value varchar(10))
create table tb4(ID int , Value varchar(10))
insert into tb1 values(1 ,'a')
insert into tb1 values(2 ,'b')
insert into tb2 values(1 ,'a')
insert into tb2 values(2 ,'b')
insert into tb2 values(3 ,'c')
insert into tb3 values(1 ,'a')
insert into tb3 values(2 ,'b')
insert into tb4 values(1 ,'a')
insert into tb4 values(2 ,'b')
insert into tb4 values(3 ,'c')
insert into tb4 values(4 ,'d')
goselect isnull(m1.id , m2.id) id , m1.Value1 , m1.Value2 , m1.value3,m2.value value4 from
(select isnull(t1.id , t2.id) id , t1.Value1 , t1.Value2 , t2.value value3 from
(select isnull(tb1.id,tb2.id) id , tb1.value value1 , tb2.value value2 from tb1 full join tb2 on tb1.id = tb2.id) t1
full join tb3 t2 on t1.id = t2.id) m1 full join tb4 m2 on m1.id = m2.iddrop table tb1 , tb2, tb3, tb4 /*
id Value1 Value2 value3 value4
----------- ---------- ---------- ---------- ----------
1 a a a a
2 b b b b
3 NULL c NULL c
4 NULL NULL NULL d(所影响的行数为 4 行)
*/
create table tb2(ID int , Value varchar(10))
create table tb3(ID int , Value varchar(10))
create table tb4(ID int , Value varchar(10))
insert into tb1 values(1 ,'a')
insert into tb1 values(2 ,'b')
insert into tb2 values(1 ,'a')
insert into tb2 values(2 ,'b')
insert into tb2 values(3 ,'c')
insert into tb3 values(1 ,'a')
insert into tb3 values(2 ,'b')
insert into tb4 values(1 ,'a')
insert into tb4 values(2 ,'b')
insert into tb4 values(3 ,'c')
insert into tb4 values(4 ,'d')
go--DROP TABLE TB1,TB2,TB3,TB4SELECT D.ID,
A.VALUE AS Value1 ,
B.Value AS Value2,
C.Value AS Value3,
D.Value AS Value4
FROM TB4 D LEFT JOIN TB1 A ON D.ID=A.ID
LEFT JOIN TB2 C ON D.ID=C.ID
LEFT JOIN TB3 B ON D.ID=B.IDID Value1 Value2 Value3 Value4
----------- ---------- ---------- ---------- ----------
1 a a a a
2 b b b b
3 NULL NULL c c
4 NULL NULL NULL d(所影响的行数为 4 行)借乌龟大侠
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-25 16:34:39
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[表1]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([ID] int,[Value] varchar(1))
insert [表1]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] int,[Value] varchar(1))
insert [表2]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--> 测试数据:[表3]
if object_id('[表3]') is not null drop table [表3]
go
create table [表3]([ID] int,[Value] varchar(1))
insert [表3]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表4]
if object_id('[表4]') is not null drop table [表4]
go
create table [表4]([ID] int,[Value] varchar(1))
insert [表4]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'
--------------开始查询--------------------------
select
d.id,a.Value as Value1,b.Value as Value2,c.Value as Value3,d.Value as Value4
from
[表4] d
full join [表1] a on
a.id=d.id
full join [表2] b on
b.id=d.id
full join [表3] c on
c.id=d.id
----------------结果----------------------------
/* id Value1 Value2 Value3 Value4
----------- ------ ------ ------ ------
1 a a a a
2 b b b b
3 NULL c NULL c
4 NULL NULL NULL d(4 行受影响)
*/
这个效果呢???
效果:
ID Value1 Value2 Value3 Value4 Value5
1 a a a a a
2 b b b b b
3 null c null c null
4 null null null d null并不能确定那个的数据是最多的 。所有连接貌似不行???新手啊。
go
create table [表1]([ID] int,[Value] varchar(1))
insert [表1]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] int,[Value] varchar(1))
insert [表2]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--> 测试数据:[表3]
if object_id('[表3]') is not null drop table [表3]
go
create table [表3]([ID] int,[Value] varchar(1))
insert [表3]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表4]
if object_id('[表4]') is not null drop table [表4]
go
create table [表4]([ID] int,[Value] varchar(1))
insert [表4]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'
--------------开始查询--------------------------
select
isnull((isnull(isnull(a.id,b.id),c.id)),d.id) as id,
a.Value as Value1,b.Value as Value2,c.Value as Value3,d.Value as Value4
from
[表1] a
full join [表2] b on
a.id=b.id
full join [表3] c on
b.id=c.id
full join [表4] d on
c.id=d.id
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([ID] int,[Value] varchar(1))
insert [表1]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] int,[Value] varchar(1))
insert [表2]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--> 测试数据:[表3]
if object_id('[表3]') is not null drop table [表3]
go
create table [表3]([ID] int,[Value] varchar(1))
insert [表3]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表4]
if object_id('[表4]') is not null drop table [表4]
go
create table [表4]([ID] int,[Value] varchar(1))
insert [表4]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'--------------开始查询--------------------------
select t.id,
a.Value as Value1, b.Value as Value2, c.Value as Value3, d.Value as Value4
from ( select a.id as id from [表1] a union
select b.id from [表2] b union
select c.id from [表3] c union
select d.id from [表4] d ) t
left join [表1] a on a.id=t.id
left join [表2] b on b.id=t.id
left join [表3] c on c.id=t.id
left join [表4] d on d.id=t.id
好像可以这样Select
ISNULL(a.Id,ISNULL(b.id,ISNULL(c.id,ISNULL(d.id,ISNULL(e.Id,'')))) ID,
a.Value as Value1,
b.Value as Value2,
c.Value as Value3,
d.Value as Value4,
e.value as Value5from tb1 aFull join tb2 b on tb1.id=tb2.id
Full join tb3 c on tb1.id=tb3.id
Full join tb4 d on tb1.id=tb4.id
Full join tb5 e on tb1.id=tb5.id
go
create table [表1]([ID] int,[Value] varchar(1))
insert [表1]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] int,[Value] varchar(1))
insert [表2]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--> 测试数据:[表3]
if object_id('[表3]') is not null drop table [表3]
go
create table [表3]([ID] int,[Value] varchar(1))
insert [表3]
select 1,'a' union all
select 2,'b'
--> 测试数据:[表4]
if object_id('[表4]') is not null drop table [表4]
go
create table [表4]([ID] int,[Value] varchar(1))
insert [表4]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'--------------开始查询--------------------------
select isnull(ab.id,cd.id) as id, ab.value1, ab.value2,
cd.value3, cd.value4
from
(select isnull(a.id,b.id) as id, a.value as value1, b.value as value2
from [表1] a full join [表2] b on a.id=b.id ) ab
full join
(select isnull(c.id,d.id) as id, c.value as value3, d.value as value4
from [表3] c full join [表4] d on c.id=d.id ) cd
on ab.id=cd.id