表A
id
1
2
表B
id
1
3
表C
id
4
5
-------------------------
请问怎么连接,结果就是:
id
1
2
3
4
5
-------------------帮帮忙啊!
id
1
2
表B
id
1
3
表C
id
4
5
-------------------------
请问怎么连接,结果就是:
id
1
2
3
4
5
-------------------帮帮忙啊!
select * from a
union
select * from b
union
select * from c
union
select * from 表b
union
select * from 表c
create table A(id int)
create table B(id int)
create table C(id int)insert into A
select 1 union
select 2insert into B
select 1 union
select 3
insert into C
select 4 union
select 5
select distinct ID from
(select * from A union all select * from B union all select * from c) T--drop table A
--drop table B
--drop table C
--------------------
ID
1
2
3
4
5
declare @表A table (id int)
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5select * from @表A union
select * from @表B union
select * from @表C/*
id
-----------
1
2
3
4
5
*/
declare @表A table (id int)
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5SELECT ISNULL(a.id,ISNULL(b.id,c.id)) AS id FROM @表A a
FULL JOIN @表B b ON a.id=b.id
FULL JOIN @表C c ON a.id=c.id
/*
id
-----------
1
2
3
4
5
*/
declare @表A table (id int)
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5select isnull(a.id,isnull(b.id,c.id))
from @表A a full join @表B b on a.id = b.id
full join @表C c on a.id = c.id
union
select * from b
union
select * from corder by id
create table t1
(
id int
)
create table t2
(
id int
)
create table t3
(
id int
)
insert into t1(id) values(1)
insert into t1(id) values(2)
insert into t2(id) values(1)
insert into t2(id) values(3)
insert into t3(id) values(4)
insert into t3(id) values(5)SELECT id FROM t1 union
SELECT id FROM t2 union
SELECT id FROM t3
/*
1
2
3
4
5
*/
表A
id
1
2
表B
id
1
3
表C
id
4
5
-------------------------
不好意思,可能我表达错误,结果要这样的:
id1 id2 id3
1 1 null
2 null null
null 3 null
null null 4
null null 4
表A
id
1
2
表B
id
1
3
表C
id
4
5
-------------------------
不好意思,可能我表达错误,结果要这样的:
id1 id2 id3
1 1 null
2 null null
null 3 null
null null 4
null null 5
declare @表A table (id int)
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5select a.id aid,b.id bid,c.id cid
from @表A a full join @表B b on a.id = b.id
full join @表C c on a.id = c.id/****************aid bid cid
----------- ----------- -----------
1 1 NULL
2 NULL NULL
NULL 3 NULL
NULL NULL 4
NULL NULL 5(5 行受影响)
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5select * from @表A a full join @表B b on a.id = b.id full join @表C c on a.id = c.id
/*
id id id
----------- ----------- -----------
1 1 NULL
2 NULL NULL
NULL 3 NULL
NULL NULL 4
NULL NULL 5(5 行受影响)
*/
declare @表A table (id int)
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5SELECT a.id,b.id,c.id FROM @表A a
FULL JOIN @表B b ON a.id=b.id
FULL JOIN @表C c ON a.id=c.id
/*
id id id
----------- ----------- -----------
1 1 NULL
2 NULL NULL
NULL 3 NULL
NULL NULL 4
NULL NULL 5
*/
create table t1
(
id int
)
create table t2
(
id int
)
create table t3
(
id int
)
insert into t1(id) values(1)
insert into t1(id) values(2)
insert into t2(id) values(1)
insert into t2(id) values(3)
insert into t3(id) values(4)
insert into t3(id) values(5)
select * from t1
select * from t2
select * from t3select * from t1 full join t2 on t1.id=t2.id full join t3 on t1.id=t3.id------------------------
id id id
1 1 NULL
2 NULL NULL
NULL 3 NULL
NULL NULL 4
NULL NULL 5
为何我在表A加上条件就不行了呢:
比如where a.id=2
加上where a.id=2 要结果是:/* id id id ----------- ----------- -----------
NULL 1 NULL
2 NULL NULL
NULL 3 NULL
NULL NULL 4
NULL NULL 5 */
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5SELECT a.id,b.id,c.id FROM
(select * from @表A WHERE id=2) a
FULL JOIN @表B b ON a.id=b.id
FULL JOIN @表C c ON a.id=c.id/*
id id id
----------- ----------- -----------
2 NULL NULL
NULL 1 NULL
NULL 3 NULL
NULL NULL 4
NULL NULL 5
*/
select a.id aid,b.id bid,c.id cid
from (select * from @表A where id = 2) a full join @表B b on a.id = b.id
full join @表C c on a.id = c.id
insert into @表A
select 1 union all
select 2declare @表B table (id int)
insert into @表B
select 1 union all
select 3declare @表C table (id int)
insert into @表C
select 4 union all
select 5SELECT case when a.id is null then
case when b.id is null then
c.id
else b.id end
else a.id end id,a.id aid,b.id bid,c.id cid FROM
(select * from @表A WHERE id=2) a
FULL JOIN @表B b ON a.id=b.id
FULL JOIN @表C c ON a.id=c.id
id aid bid cid
----------- ----------- ----------- -----------
2 2 NULL NULL
1 NULL 1 NULL
3 NULL 3 NULL
4 NULL NULL 4
5 NULL NULL 5
比如where a.id=2这个能解决
各位大大,昨天那个还有问题啊:id id id
----------- ----------- -----------
2 NULL NULL
NULL 1 NULL
NULL 3 NULL
NULL NULL 3
NULL NULL 5我要的结果是
id id id
----------- ----------- -----------
2 NULL NULL
NULL 1 NULL
NULL 3 3
NULL NULL 5