有如下四个表create table table1(
id int identity(1,1),
a int default 0,
b datetime default getdate(),
primary key(id)
);
create table table2(
id2 int identity(1,1),
top_id int default 0, -- 该值与table1中的id对应,即为table1.id的值
c nvarcar(20),
d int default 0,
);
create table table3(
id3 int identity(1,1),
top_id int default 0, -- 该值与table1中的id对应,即为table1.id的值
c nvarcar(20),
d int default 0,
);
create table table4(
id4 int identity(1,1),
top_id int default 0, -- 该值与table1中的id对应,即为table1.id的值
c nvarcar(20),
d int default 0,
);
-- table2,table3,table4的值是相同的
-- 现在有如下数据
table1 table2 table3 table4
id a b id2 top_id c d id3 top_id c d id4 top_id c d
1 2 2008-4-25 22:30:01 1 1 'aa' 0 1 2 'bb' 0 1 3 'bb' 0
2 5 2008-4-25 22:30:01 2 4 'bb' 1 2 5 'cc' 3
3 6 2008-4-25 22:30:01
4 7 2008-4-25 22:30:01
5 8 2008-4-25 22:30:01
-- 前提是table1中的id的值不知道会对应在table2,table3或table4中
-- 要求查找任意table1中id对应值的结果,如:要查询table1中id值为1的结果为
id a b id2 top_id c d
1 2 2008-4-25 22:30:01 1 1 'aa' 0
-- 若要查询table1.id=5的值为
id a b id4 top_id c d
5 8 2008-4-25 22:30:01 2 5 'cc' 3
id int identity(1,1),
a int default 0,
b datetime default getdate(),
primary key(id)
);
create table table2(
id2 int identity(1,1),
top_id int default 0, -- 该值与table1中的id对应,即为table1.id的值
c nvarcar(20),
d int default 0,
);
create table table3(
id3 int identity(1,1),
top_id int default 0, -- 该值与table1中的id对应,即为table1.id的值
c nvarcar(20),
d int default 0,
);
create table table4(
id4 int identity(1,1),
top_id int default 0, -- 该值与table1中的id对应,即为table1.id的值
c nvarcar(20),
d int default 0,
);
-- table2,table3,table4的值是相同的
-- 现在有如下数据
table1 table2 table3 table4
id a b id2 top_id c d id3 top_id c d id4 top_id c d
1 2 2008-4-25 22:30:01 1 1 'aa' 0 1 2 'bb' 0 1 3 'bb' 0
2 5 2008-4-25 22:30:01 2 4 'bb' 1 2 5 'cc' 3
3 6 2008-4-25 22:30:01
4 7 2008-4-25 22:30:01
5 8 2008-4-25 22:30:01
-- 前提是table1中的id的值不知道会对应在table2,table3或table4中
-- 要求查找任意table1中id对应值的结果,如:要查询table1中id值为1的结果为
id a b id2 top_id c d
1 2 2008-4-25 22:30:01 1 1 'aa' 0
-- 若要查询table1.id=5的值为
id a b id4 top_id c d
5 8 2008-4-25 22:30:01 2 5 'cc' 3
(
select * from table2
union all
select * from table3
union all
select * from table4
) as b
on a.id = b.top_id
where a.id = 1
(
select * from table2
union all
select * from table3
union all
select * from table4
) as b
on a.id = b.top_id
where a.id = 5
(
select a.* , b.* from table1 a , table2 b where a.id = b.top_id
union all
select a.* , b.* from table1 a , table3 b where a.id = b.top_id
union all
select a.* , b.* from table1 a , table4 b where a.id = b.top_id
) t
where id = 1 (or id = 5)
select
a.*,
id2=coalesce(b.id2,c.id3,d.id4),
c=coalesce(b.c,c.c,d.c),
d=coalesce(b.d,c.d,d.d)
from #table1 a
left join #table2 b on a.id=b.top_id
left join #table3 c on a.id=c.top_id
left join #table4 d on a.id=d.top_id
where a.id=1 and coalesce(b.id2,c.id3,d.id4) is not null
--where a.id=5 and coalesce(b.id2,c.id3,d.id4) is not null
@newid int
as
begin
if(exists(select * from table2 where top_id=@newid))
begin
select a.*,b.* from table1 a join table2 b on a.id=b.top_id
end
else if(exists(select * from table3 where top_id=@newid))
begin
select a.*,b.* from table1 a join table3 b on a.id=b.top_id
end
else if(exists(select * from table4 where top_id=@newid))
begin
select a.*,b.* from table1 a join table4 b on a.id=b.top_id
end
end
@newid int
as
begin
if(exists(select * from table2 where top_id=@newid))
begin
select a.*,b.* from table1 a join table2 b on a.id=b.top_id where a.id=@newid
end
else if(exists(select * from table3 where top_id=@newid))
begin
select a.*,b.* from table1 a join table3 b on a.id=b.top_id where a.id=@newid
end
else if(exists(select * from table4 where top_id=@newid))
begin
select a.*,b.* from table1 a join table4 b on a.id=b.top_id where a.id=@newid
end
end
(
select * from table2
union all
select * from table3
union all
select * from table4
) as b
on a.id = b.top_id
where a.id = 1