34,588
社区成员
发帖
与我相关
我的任务
分享
select (case
when tableA.ID is not null Then tableA.ID
when tableB.ID is not null Then tableB.ID
else tableC.ID end) AS ID,
name,area,career
from tableA
full outer join tableB on tableA.ID=tableB.ID
full outer join tableC on tableA.ID=tableC.ID
order by ID
SELECT COALESCE(A.ID, B.ID, C.ID) AS ID, [NAME], AREA, CAREER
FROM TA A
FULL JOIN TB B
ON A.ID = B.ID
FULL JOIN TC C
ON ISNULL(A.ID, B.ID) = C.ID
create table tableA
(
ID int,
name varchar(20)
)
insert into tableA select 1,'张三'
insert into tableA select 2,'李四'
insert into tableA select 3,'王五'
insert into tableA select 7,'赵六'
insert into tableA select 8,'孙七'
create table tableB
(
ID int,
area varchar(20)
)
insert into tableB select 1,'北京'
insert into tableB select 3,'上海'
insert into tableB select 4,'天津'
insert into tableB select 5,'青岛'
insert into tableB select 9,'成都'
create table tableC
(
ID int,
career varchar(20)
)
insert into tableC select 2,'学生'
insert into tableC select 3,'工人'
insert into tableC select 5,'教师'
insert into tableC select 6,'警察'
insert into tableC select 7,'医生'
;with hgo as
(
select p.number AS ID,A.name from tableA A
right join
(
select number from master..spt_values where type='p'
) p on p.number=A.ID where number<=
(select max(ID)
from
(
select A.ID from tableA A
union all
select B.ID from tableB B
union all
select C.ID from tableC C
) tt) and number>0
)
select h.*,B.area,C.career from hgo h left join tableB B
on h.ID=B.ID left join tableC C
on C.ID=h.ID
ID name area career
----------- -------------------- -------------------- --------------------
1 张三 北京 NULL
2 李四 NULL 学生
3 王五 上海 工人
4 NULL 天津 NULL
5 NULL 青岛 教师
6 NULL NULL 警察
7 赵六 NULL 医生
8 孙七 NULL NULL
9 NULL 成都 NULL
-->sinpoal
--->建表
if object_id('[TA]') is not null drop table [TA]
create table [TA]([ID] int,[name] varchar(4))
insert [TA]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 7,'赵六' union all
select 8,'孙七'
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[area] varchar(4))
insert [TB]
select 1,'北京' union all
select 3,'上海' union all
select 4,'天津' union all
select 5,'青岛' union all
select 9,'成都'
if object_id('[TC]') is not null drop table [TC]
create table [TC]([ID] int,[career] varchar(4))
insert [TC]
select 2,'学生' union all
select 3,'工人' union all
select 5,'教师' union all
select 6,'警察' union all
select 7,'医生'
-------------SQL 查询
select ta.* ,tb.area into table1 from ta full join tb
on ta.id=tb.id
select table1.*,tc.career from table1 full join tc
on table2.id=tc.id
-------- 执行
/*
1 张三 北京 NULL
2 李四 NULL 学生
3 王五 上海 工人
7 赵六 NULL 医生
8 孙七 NULL NULL
NULL NULL 天津 NULL
NULL NULL 青岛 NULL
NULL NULL 成都 NULL*/
CREATE TABLE [TableA]([ID] [int], [name] [nchar](10) ) ON [PRIMARY]
CREATE TABLE [TableB]([ID] [int], [area] [nchar](10)) ON [PRIMARY]
CREATE TABLE [TableC]([ID] [int], [career] [nchar](10) ) ON [PRIMARY]
INSERT INTO [TableA] VALUES (1,'张三')
INSERT INTO [TableA] VALUES (2,'李四')
INSERT INTO [TableA] VALUES (3,'王五')
INSERT INTO [TableA] VALUES (7,'赵六')
INSERT INTO [TableA] VALUES (8,'孙七')
INSERT INTO [TableB] VALUES (1,'北京')
INSERT INTO [TableB] VALUES (3,'上海')
INSERT INTO [TableB] VALUES (4,'天津')
INSERT INTO [TableB] VALUES (5,'青岛')
INSERT INTO [TableB] VALUES (6,'成都')
INSERT INTO [TableC] VALUES (2,'学生')
INSERT INTO [TableC] VALUES (3,'工人')
INSERT INTO [TableC] VALUES (5,'教师')
INSERT INTO [TableC] VALUES (6,'警察')
INSERT INTO [TableC] VALUES (7,'医生')
SELECT
ISNULL(A.ID,ISNULL(B.ID,C.ID))AS ID ,
A.NAME,B.AREA,C.CAREER
FROM
[TableA] A
FULL JOIN [TableB] B ON
A.ID=B.ID
FULL JOIN [TableC] C ON
A.ID=C.ID
order by 1
drop table [TableA],[Tableb],[Tablec]
/*ID NAME AREA CAREER
----------- ---------- ---------- ----------
1 张三 北京 NULL
2 李四 NULL 学生
3 王五 上海 工人
4 NULL 天津 NULL
5 NULL 青岛 NULL
5 NULL NULL 教师
6 NULL NULL 警察
6 NULL 成都 NULL
7 赵六 NULL 医生
8 孙七 NULL NULL
(10 行受影响)
*/
CREATE TABLE [TableA]([ID] [int], [name] [nchar](10) ) ON [PRIMARY]
CREATE TABLE [TableB]([ID] [int], [area] [nchar](10)) ON [PRIMARY]
CREATE TABLE [TableC]([ID] [int], [career] [nchar](10) ) ON [PRIMARY]
INSERT INTO [TableA] VALUES (1,'张三')
INSERT INTO [TableA] VALUES (2,'李四')
INSERT INTO [TableA] VALUES (3,'王五')
INSERT INTO [TableA] VALUES (7,'赵六')
INSERT INTO [TableA] VALUES (8,'孙七')
INSERT INTO [TableB] VALUES (1,'北京')
INSERT INTO [TableB] VALUES (3,'上海')
INSERT INTO [TableB] VALUES (4,'天津')
INSERT INTO [TableB] VALUES (5,'青岛')
INSERT INTO [TableB] VALUES (6,'成都')
INSERT INTO [TableC] VALUES (2,'学生')
INSERT INTO [TableC] VALUES (3,'工人')
INSERT INTO [TableC] VALUES (5,'教师')
INSERT INTO [TableC] VALUES (6,'警察')
INSERT INTO [TableC] VALUES (7,'医生')
select (case
when tableA.ID is not null Then tableA.ID
when tableB.ID is not null Then tableB.ID
else tableC.ID end) AS ID,
name,area,career
from tableA
full outer join tableB on tableA.ID=tableB.ID
full outer join tableC on tableA.ID=tableC.ID
order by ID
--> 测试数据:[TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA]([ID] int,[name] varchar(4))
insert [TA]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 7,'赵六' union all
select 8,'孙七'
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[area] varchar(4))
insert [TB]
select 1,'北京' union all
select 3,'上海' union all
select 4,'天津' union all
select 5,'青岛' union all
select 9,'成都'
if object_id('[TC]') is not null drop table [TC]
create table [TC]([ID] int,[career] varchar(4))
insert [TC]
select 2,'学生' union all
select 3,'工人' union all
select 5,'教师' union all
select 6,'警察' union all
select 7,'医生'
select ID=isnull(TA.ID,isnull(TB.ID,TC.ID)),
[name]=max(name),
[area]=max(area),
[career]=max(career)
from [TA] full join TB on TA.ID=TB.ID full join TC ON TB.ID=TC.ID
group by isnull(TA.ID,isnull(TB.ID,TC.ID))
/*
ID name area career
----------- ---- ---- ------
1 张三 北京 NULL
2 李四 NULL 学生
3 王五 上海 工人
4 NULL 天津 NULL
5 NULL 青岛 教师
6 NULL NULL 警察
7 赵六 NULL 医生
8 孙七 NULL NULL
9 NULL 成都 NULL
*/
drop table TA,TB,TC
SELECT
ISNULL(A.ID,ISNULL(B.ID,C.ID))AS ID ,
A.NAME,B.AREA,C.CAREER
FROM A FULL JOIN B ON A.ID=B.ID FULL JOIN C ON A.ID=C.ID
select A.ID,A.name,B.area,C.career from tableA A left join table B on
A.ID=B.ID join tableC C on A.ID=C.ID