求一条SQL语句或者存储过程

[kidhawk]

表：table1,table2,table3,table4
字段：
tabel1: ID, A
table2:ID
table3:ID
table4:ID,B
要求：
从Table1和table4里面查找A字段和B字段。并且table1的ID在table2或者table3里面，table4的ID在table2或者table3里面。
但是table1的ID和table4的ID有可能重复，此时就要将查出的记录合并。
例如：table1里的ID有1，2，3；字段A里面是a，b，c
table4里的ID有3，4，5；字段B里面是d,e,f
table2里的ID有2，3
table3里的字段有3，4，5
那么查询结果应该是：

ID，A，B

2 b，null
3 c，d
4 null，e
5 null，f

请问用一条SQL语句怎么写？或者用试图，存储过程实现也可以

[yangys]

insert #tmp
select id from table2
union all
select id from table3

select
id,
(select A from table1 where table1.id=id)as A,
(select B from table1 where table2.id=id)as B
from #tmp order by id

--试试这样能否行,没时间测试了

[vivianfdlpw]

create function f_str(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''

if exists(select 1 from table1 where ID=@id)
begin
select @str=@str+','+A
from table1 where ID=@id
set @str=stuff(@str,1,1,'')
end
else
begin
set @str=@str+'null'
end

if exists(select 1 from table2 where ID=@id)
begin
select @str=@str+','+B from table2 where ID=@id
end
begin
set @str=@str+',null'
end

return @str
end
go


--查询
select ID,
dbo.f_str(ID)
from (
select ID
from (select ID from table1 union select ID from table4)tt
where ID in(select ID from table2 union select ID from table3)
)t

[jxdjxd1111]

declare @b1 table (id int,a varchar(10))
insert into @b1 select 1,'a'
union all select 2,'b'
union all select 3,'c'

declare @b2 table (id int)
insert into @b2 select 2
union all select 3

declare @b3 table (id int)
insert into @b3 select 3
union all select 4
union all select 5

declare @b4 table (id int,b varchar(10))
insert into @b4 select 3,'d'
union all select 4,'e'
union all select 5,'f'

create table #tmp(id int)
insert into #tmp
select id from @b2
union all
select id from @b3

select
distinct id,
(select a from @b1 where id=k.id) p,
(select b from @b4 where id=k.id) q
from #tmp k order by id
drop table #tmp

测试结果
ID p q
2 b null
3 c d
4 null e
5 null f

[vivianfdlpw]

create table table1(id int,a varchar(10))
insert into table1
select 1,'a' union all
select 2,'b' union all
select 3,'c'

create table table2(id int)
insert into table2
select 2 union all
select 3

create table table3(id int)
insert into table3
select 3 union all
select 4 union all
select 5

create table table4(id int,b varchar(10))
insert into table4
select 3,'d' union all
select 4,'e' union all
select 5,'f'
go

--创建函数
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''

if exists(select 1 from table1 where ID=@id)
begin
select @str=@str+','+A
from table1 where ID=@id
set @str=stuff(@str,1,1,'')
end
else
begin
set @str=@str+'null'
end

if exists(select 1 from table4 where ID=@id)
begin
select @str=@str+','+B from table4 where ID=@id
end
else
begin
set @str=@str+',null'
end

return @str
end
go


--查询
select ID,
dbo.f_str(ID) as 'A,B'
from (
select ID
from (select ID from table1 union select ID from table4)tt
where ID in(select ID from table2 union select ID from table3)
)t




--删除测试环境
drop function f_str
drop table table1,table2,table3,table4

--结果
/*
ID A,B
----------- -----------
2 b,null
3 c,d
4 null,e
5 null,f

(4 row(s) affected)
*/

[yangys]

--终于有点时间了.经测试这样可以:
declare @b1 table (id int,a varchar(10))
insert into @b1 select 1,'a'
union all select 2,'b'
union all select 3,'c'

declare @b2 table (id int)
insert into @b2 select 2
union all select 3

declare @b3 table (id int)
insert into @b3 select 3
union all select 4
union all select 5

declare @b4 table (id int,b varchar(10))
insert into @b4 select 3,'d'
union all select 4,'e'
union all select 5,'f'

create table #tmp(id int)
insert into #tmp
select id from @b2
union
select id from @b3

select
id,
(select top 1 A from @b1 where id=a.id)as A,
(select top 1 B from @b4 where id=a.id)as B
from #tmp a order by id

drop table #tmp

[MorningTea]

回复人： kidhawk(小鹰) ( ) 信誉：100 2005-09-08 10:28:00 得分: 0


vivianfdlpw() :

你怎么将A字段和B字段合并成一个字段了？这不是我想要的结果。


--可是你列出来就是如此的写法哦，难道不是吗？到底结果集合要多少栏？
ID，A，B

2 b，null
3 c，d
4 null，e
5 null，f

[kidhawk]

vivianfdlpw() :

你怎么将A字段和B字段合并成一个字段了？这不是我想要的结果。