这个SQL语句如何写？或者怎样修改表结构？

[oldsky]

表A:NodeID (int,主键),NodeName (nvarchar),...
表B:TestID (int,主键),TestName (nvarchar),...
表C:QuesID (int,主键),QuesName (nvarchar),...
表D:NodeID ，TestID, ReType (char),...
现在要根据表D查出NodeName或TestName或QuesName,最后结果为：
ID Name (别名)
2 fff
1 ddd
...

（ReType为D时，需要查表A得到NodeName；ReType为T时，需要查表B得到TestName;ReType为Q时，需要查表C得到QuesName）

[newdongkui]

用union或者用case，这里我说case的写法

select (case retype when 'A' then d.NodeID when 'T' then d.testid when 'Q' then d.QuesID end) as id , (case retype when 'A' then a.nodename when 'T' then b.testname when 'Q' then c.quesname end) as name
from d
inner join a on a.nodeid = d.nodeid
inner join b on b.testid = d.testid
inner join c on c.quesid = d.quesid

[pengdali]

select case reType
when 'D' then (select NodeName from A where NodeID=D.TestID)
when 'T' then (select TestName from B where TestID=D.TestID)
when 'Q' then (select QuesName from C where QuesID=D.TestID)
end
from D

[pengdali]

或：

select case reType when 'D' then a.NodeName when 'T' then b.TestName when 'Q' then q.QuesName end
from D left join a on a.NodeID=D.TestID
left join B on b.TestID=D.TestID
left join C on c.QuesID=D.TestID

[Rewiah]

楼主问题是不是写错了，表d的结构应该是ID,ReType (char),...
或者NodeID ，TestID,QuesID, ReType (char),...


如果表结构是NodeID ，TestID,QuesID, ReType (char),...

CASE 写法：
select D.ID,(case D.retype when 'D' then d.NodeID when 'T' then d.testid when 'Q' then d.QuesID end) as id , (case retype when 'D' then a.nodename when 'T' then b.testname when 'Q' then c.quesname end) as name
from d
LEFT join a on a.id = d.nodeid
LEFT join b on b.id = d.testid
LEFT join c on c.id = d.quesid

推荐UNION ALL写法：
SELECT D.testid AS ID,A.NODENAME AS NAME
FROM D,A
WHERE D.retype ='D'
AND D.NodeID=A.NodeID
UNION ALL
SELECT D.testid AS ID,B.TESTNAME AS NAME
FROM D,B
WHERE D.retype ='T'
AND D.testid =B.testid
UNION ALL
SELECT D.QuesID AS ID,C.QuesNAME AS NAME
FROM D,C
WHERE D.retype ='Q'
AND D.QuesID =C.QuesID

[灯火GG]

select case reType
when 'D' then (select NodeName from A where NodeID=D.TestID)
when 'T' then (select TestName from B where TestID=D.TestID)
when 'Q' then (select QuesName from C where QuesID=D.TestID)
end
from D

[愉快的登山者]

表D:NodeID ，TestID, ReType (char),...
可以修改成：
1。表D:ID ，ReType (char),...
2。表D:NodeID ，TestID, Quesid, ReType (char),...
我认为1比较好。
针对修改后的表D，建立查询语句：
select D.id, (case D.retype when 'A' then A.nodename
when 'T' then B.testname
when 'Q' then C.quesname
end ) as Name from D
left join A on A.NodeID = D.id
left join B on B.NodeID = D.id
left join C on C.NodeID = D.id

[oldsky]

问题原意如下：
表D是一个回收站表，表A-表C是三个不同类型的文件表，由于各种类型文件的属性和字段不同，就用了3个表，但基本的文件ID和文件名这2个字段都有。当我进入回收站时，看到的只是那些文件名就可以了。现在我要用语句查出这些文件名就可以了。

[pengdali]

我不是写对了吗？

select case reType
when 'D' then (select NodeName from A where NodeID=D.TestID)
when 'T' then (select TestName from B where TestID=D.TestID)
when 'Q' then (select QuesName from C where QuesID=D.TestID)
end
from D

[oldsky]

多谢各位

[yiyanFengZi]

declare @2 table (y int,b char(10))
insert @2 values (1,'a')
insert @2 values (2,'d')
insert @2 values (3,'c')
insert @2 values (4,'d')

declare @b table(a int,b char(1))
insert @b values(1,'1')
insert @b values(3,'3')

declare @a table(a int,b int,type Char(1))
insert @a values(4,1,'A')
insert @a values(2,3,'B')


Select * From @a
Select * From @b
Select * From @2
Select (case d.type When 'A' Then d.a
When 'B' Then d.b end) as ID,(case d.type When 'A' Then (select b From @2 A Where A.y=d.a)
When 'B' Then (Select b From @b B where b.a=d.b) end) as name from @a d

[oldsky]

pengdali(大力)：你的报错：当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。
谢谢 tj_dns(愉快的登山者) 、 newdongkui(老乌鸦)、Rewiah(乘长风) ( )