请大家帮我写一写这条SQL查询语句

[Boible]

表1

UID TITLE FIRSTNAME LASTNAME
0 MR A0 A00
1 MR A1 A11
2 MR A2 A22
3 MR A3 A33
4 MR A4 A44
5 MR A5 A55


表2

GID UID1 UID2 UID3 UID4
100 0 1 2
200 3 4
300 5


要求的查询结果

GID NAMES
100 A0 A00/A1 A11/A2 A33
200 A3 A33/A4 A44
300 A5 A55

[wgsasd311]

--try
create function dbo.fn_str(@gid int)
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+'/'+t2.firstname+' '+t2.lastname from t1,t2 where t2.gid=@gid and
(t1.uid=t2.uid1 or t1.uid=t2.uid2 or t1.uid=t2.uid3 or t1.uid=t2.uid4 )
return @s
end
go
select gid ,names=dbo.fn_str(gid) from tb

[冷箫轻笛]

select t1.GID,isnull(t2.FIRSTNAME+' '+t2.LASTNAME,'')+
isnull('/'+t3.FIRSTNAME+' '+t3.LASTNAME,'')+
isnull('/'+t4.FIRSTNAME+' '+t4.LASTNAME,'')+
isnull('/'+t5.FIRSTNAME+' '+t5.LASTNAME,'')
from table2 t1 left join table1 t2 on t1.UID1 = t2.UID
left join table1 t3 on t1.UID1 = t3.UID
left join table1 t4 on t1.UID1 = t4.UID
left join table1 t5 on t1.UID1 = t5.UID

[fcuandy]

试试

SELECT GID,Names=
NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM uTable WHERE uid1=uid),' /')
+NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM uTable WHERE uid2=uid),' /')
+NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM uTable WHERE uid3=uid)+'/',' /')
+NULLIF((SELECT FirstName + ' ' + LastName FROM uTable WHERE uid4=uid),' ')

因为觉得这个没什么要求的地方,就随便写了一下.

[wgsasd311]

create table t1(UID int,TITLE varchar(10),FIRSTNAME varchar(10),LASTNAME varchar(10))
insert into t1
select 0,'MR','A0','A00' union all
select 1,'MR','A1','A11' union all
select 2,'MR','A2','A22' union all
select 3,'MR','A3','A33' union all
select 4,'MR','A4','A44' union all
select 5,'MR','A5','A55'
create table t2(GID int,UID1 int, UID2 int, UID3 int, UID4 int)
insert into t2
select 100,0,1,2,null union all
select 200,3,4,null,null union all
select 300,5,null,null,null

go


create function fn_str(@gid int)
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+'/'+t1.firstname+' '+t1.lastname from t1,t2 where t2.gid=@gid and
(t1.uid=t2.uid1 or t1.uid=t2.uid2 or t1.uid=t2.uid3 or t1.uid=t2.uid4 )
return stuff(@s,1,1,'')
end
go
select gid ,names=dbo.fn_str(gid) from t2
--
drop table t1,t2
drop function fn_str

[fcuandy]

我上面随手写的,有点问题,改下就OK

SELECT GID,LEFT(Names,LEN(Names)-1) FROM(
SELECT GID,Names=
ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid1=uid),' /'),'')
+ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid2=uid),' /'),'')
+ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid3=uid),' /'),'')
+ISNULL(NULLIF((SELECT FirstName + ' ' + LastName FROM t1 WHERE uid4=uid),' '),'') FROM t2) BASE

[fcuandy]

上面的写法,如果uid4有值就有点问题了,应该是

SELECT GID,LEFT(Names,LEN(Names)-1) FROM(
SELECT GID,Names=
ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid1=uid),' /'),'')
+ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid2=uid),' /'),'')
+ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid3=uid),' /'),'')
+ISNULL(NULLIF((SELECT FirstName + ' ' + LastName +'/' FROM t1 WHERE uid4=uid),' /'),'') FROM t2) BASE

[$扫地僧$]

create table t1(UID int,TITLE varchar(10),FIRSTNAME varchar(10),LASTNAME varchar(10))
insert into t1
select 0,'MR','A0','A00' union all
select 1,'MR','A1','A11' union all
select 2,'MR','A2','A22' union all
select 3,'MR','A3','A33' union all
select 4,'MR','A4','A44' union all
select 5,'MR','A5','A55'

create table t2(GID int,UID1 int, UID2 int, UID3 int, UID4 int)
insert into t2
select 100,0,1,2,null union all
select 200,3,4,null,null union all
select 300,5,null,null,null


select GID,
case when T.UID1 is not null then (select FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID1) else '' end
+ case when (T.UID1 is not null) and (T.UID2 is not null) then (select '/' + FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID2)
when (T.UID2 is not null) then (select FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID2)
else '' end
+ case when (T.UID2 is not null) and (T.UID3 is not null) then (select '/' + FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID3)
when (T.UID3 is not null) then (select FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID3)
else '' end
+ case when (T.UID3 is not null) and (T.UID4 is not null) then (select '/' + FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID4)
when (T.UID4 is not null) then (select FIRSTNAME + ' ' + LASTNAME from t1 where UID=T.UID4)
else '' end
from t2 T