设有两个表;
TreeView
字段:nodeID parentID nodeText
1 0 a
2 1 b
3 1 c
DetailInfo
字段:info1 info2 nodeID parentID
qq1 qq2 2 1
qq10 qq20 3 1查询结果要求这样: info1 info2 nodeID nodeText parentNodeText
qq1 qq2 2 b a
qq10 qq20 3 c a 什么方法比较好,因为DetailInfo记录比较多
TreeView
字段:nodeID parentID nodeText
1 0 a
2 1 b
3 1 c
DetailInfo
字段:info1 info2 nodeID parentID
qq1 qq2 2 1
qq10 qq20 3 1查询结果要求这样: info1 info2 nodeID nodeText parentNodeText
qq1 qq2 2 b a
qq10 qq20 3 c a 什么方法比较好,因为DetailInfo记录比较多
from TreeView A,DetailInfo B,TreeView c
where A.nodeid=B.nodeid and A.parentid=C.nodeid
m.info2 ,
m.nodeID ,
(select nodetext from treeview n where nodeid = m.nodeid) nodetext,
(select nodeText from treeview n where nodeid = m.parentID) parentNodeText
from DetailInfo m
insert into treeView values(1 , 0 , 'a')
insert into treeView values(2 , 1 , 'b')
insert into treeView values(3 , 1 , 'c')
create table DetailInfo(info1 varchar(10) , info2 varchar(10) , nodeID int,parentID int)
insert into DetailInfo values('qq1' , 'qq2' , 2 , 1)
insert into DetailInfo values('qq10', 'qq20', 3 , 1)
goselect m.info1 ,
m.info2 ,
m.nodeID ,
(select nodetext from treeview n where nodeid = m.nodeid) nodetext,
(select nodeText from treeview n where nodeid = m.parentID) parentNodeText
from DetailInfo mdrop table treeView , DetailInfo/*
info1 info2 nodeID nodetext parentNodeText
---------- ---------- ----------- ---------- --------------
qq1 qq2 2 b a
qq10 qq20 3 c a(所影响的行数为 2 行)
*/
SELECT X.info1,X.info2,X.nodeID,Y.nodeText,Y.parentNodeText
FROM DetailInfo X
INNER JOIN (SELECT A.nodeID,A.parentID,A.nodeText,B.nodeText AS parentNodeText FROM TreeView A
LEFT JOIN TreeView B ON A.parentID = B.nodeID
) Y ON X.nodeID = Y.nodeID