select name where id=(select parentid where id=子接点ID).child字段为多余字段.
下面这个勉强能用create table test ( id int primary key, name varchar(10), parentid int, child bit ) insert into test values(1,'aa',0,0) insert into test values(2,'bb',1,1) insert into test values(3,'cc',1,1) insert into test values(4,'dd',2,1)select t.id,t.name,t.name, t.parentid, t.child from test t where t.parentid = 0 union select l.id,l.name,r.name, l.parentid, l.child from test l,test r where l.parentid = r.id
可以只用一個Select實現Select A.*, (Case When child = 1 Then A.name Else B.name End) As parentname From 表 A Left Join 表 B On A.parentid = B.id
有點問題,掉了一個別名Select A.*, (Case When A.child = 1 Then A.name Else B.name End) As parentname From 表 A Left Join 表 B On A.parentid = B.id
--創建測試環境 Create Table 表 (id Int, name Varchar(10), parentid Int, child Bit) Insert 表 Select 1, 'A', 0, 1 Union All Select 2, 'B', 0, 1 Union All Select 3, 'A1', 1, 0 GO --測試 Select A.*, (Case When A.child = 1 Then A.name Else B.name End) As parentname From 表 A Left Join 表 B On A.parentid = B.id GO --刪除測試環境 Drop Table 表 --結果 /* id name parentid child parentname 1 A 0 1 A 2 B 0 1 B 3 A1 1 0 A */
用程序写递归
(
id int primary key,
name varchar(10),
parentid int,
child bit
)
insert into test values(1,'aa',0,0)
insert into test values(2,'bb',1,1)
insert into test values(3,'cc',1,1)
insert into test values(4,'dd',2,1)select t.id,t.name,t.name, t.parentid, t.child
from test t
where t.parentid = 0
union
select l.id,l.name,r.name, l.parentid, l.child
from test l,test r
where l.parentid = r.id
A.*,
(Case When child = 1 Then A.name Else B.name End) As parentname
From
表 A
Left Join
表 B
On A.parentid = B.id
A.*,
(Case When A.child = 1 Then A.name Else B.name End) As parentname
From
表 A
Left Join
表 B
On A.parentid = B.id
Create Table 表
(id Int,
name Varchar(10),
parentid Int,
child Bit)
Insert 表 Select 1, 'A', 0, 1
Union All Select 2, 'B', 0, 1
Union All Select 3, 'A1', 1, 0
GO
--測試
Select
A.*,
(Case When A.child = 1 Then A.name Else B.name End) As parentname
From
表 A
Left Join
表 B
On A.parentid = B.id
GO
--刪除測試環境
Drop Table 表
--結果
/*
id name parentid child parentname
1 A 0 1 A
2 B 0 1 B
3 A1 1 0 A
*/