Ⅰ 根據子表如何查詢對應的父表
父表需建立主索引，子表只需要建立普通索引，子表 left join 主表
Ⅱ sql同時查詢子、父節點的值
直接查就行了，也可以試一下下面的：
select name,value,(select ParentValue from b) as ParentValue from a where OrgID = '001' ;
Ⅲ sql2005 根據子ID遞歸查詢所有父ID，如下，問:dbo怎麼才能否去掉
因為你的with後面的名稱和表的名稱一樣了，沖突了。
WITH XI_news_Class 改成with 其他名稱
Ⅳ SQL （根據子節點查詢父節點信息）
declare @lt table(id int,level int)
declare @level int
declare @findid int
--初始化數據
set @findid = 25/*找nodeId = 25*/
--end of 初始化數據
set @level = 1
insert @lt select @findid,@level
while @@rowcount > 0
begin
set @level = @level + 1
insert @lt select a. parentId,@level
from 你的表名 a, @lt b
where a.nodeId = b.nodeId and b.level = @level - 1
end
--連接得到結果
select a.*
from 你的表名 a inner join @lt b
on a.nodeId=b.nodeId
Ⅳ 如何用sql查找出父子關系關聯的結果集
加多一列層級碼，會簡單很多比如亞洲用『01』 中國用『0101』，北京用『010101『，海定用『01010101』，東城用』01010102『
這樣語句可以
select id as col1,(select name from 表 where code = SubString(a.Code, 1,2)) as col2,
(select name from 表 where code = SubString(a.Code, 3,2)) as col3,
(select name from 表 where code = SubString(a.Code, 5,2)) as col4,
(select name from 表 where code = SubString(a.Code, 7,2)) as col5
from 表 a where Type = 'Distric'
不然
(select d.name from 表 b, 表 c, 表 d where a.parentid = b.id and b.parentid = c.id
and c.parentid = d.id) as col2,...
Ⅵ 求高手幫忙sql寫法：樹節點放一個表中，怎麼用一條語句查詢一個節點及對應的所有父節點信息。
建議使用遞歸，
oracl語法示例如下、
CREATE TABLE TBL_TEST
(
ID NUMBER, --主鍵
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0 --------父節點主鍵
);
插入測試數據：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
從Root往樹末梢遞歸
select * from TBL_TEST
start with id=1
connect by prior id = pid
從末梢往樹ROOT遞歸
select * from TBL_TEST
start with id=5
connect by prior pid = id
SQL server 2005語法示例如下、
CREATE TABLE TBL_TEST
(
ID int,
NAME VARCHAR(100),
PID int DEFAULT 0
);
插入測試數據：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
select * from TBL_TEST
--從Root往樹末梢遞歸
with cte as
(select *,0 as TLevel from TBL_TEST where ID=1
union all
select t1.*,t2.TLevel+1 from TBL_TEST t1 inner join cte t2 on t1.PID=t2.ID)
select * from cte
--從末梢往樹ROOT遞歸
with cte as
(select *,0 as TLevel from TBL_TEST where ID=5
union all
select t1.*,t2.TLevel+1 from TBL_TEST t1 inner join cte t2 on t1.ID=t2.PID)
select * from cte
Ⅶ sql根據子節點查出所有的父節點的
用函數做，根據你的表結構改：
父節點查詢子節點
create function GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert into @t select id from table where parent_id = @ParentID
while @@rowcount<>0
begin
insert into @t select a.id from table as a
inner join @t as b
on a.parent_id = b.ID
and not exists(select 1 from @t where ID=a.ID)
end
return
end
go
子節點查詢父節點
create function GetParentID(@ChildID int)
returns @t table(PID int)
as
begin
insert into @t select parent_id from table where ID=@ChildID
while @@rowcount<>0
begin
insert into @t select a.parent_id from table as a
inner join @t as b
on a.ID=b.PID
and not exists(select 1 from @t where PID=a.parent_id)
end
return
end
go
Ⅷ sql 父類下的子類查詢的方法，包含父類的信息
-查詢各節點的父路徑函數(從父到子)
create function f_pid1(@id varchar(3)) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid is not null)
begin
select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
go
--查詢各節點的父路徑函數(從子到父)
create function f_pid2(@id varchar(3)) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid is not null)
begin
select @id = b.id , @re_str = @re_str + ',' + b.name from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
go
select * ,
dbo.f_pid1(id) [路徑(從父到子)] ,
dbo.f_pid2(id) [路徑(從子到父)]
from tb order by id
drop function f_pid1 , f_pid2
drop table tb
/*
id pid name 路徑(從父到子) 路徑(從子到父)
Ⅸ SQL2000 用子節點查最高父節點，並計算相乘數量。
create table tmp
(
PARTNAME varchar(50),
QTY int,
CHILD_ID varchar(50),
PARENT_ID varchar(50)
)
GO
declare @name varchar(50)
set @name='AA'
insert into tmp
select distinct t2.PARTNAME, t1.QTY*t2.QTY,t2.CHILD_ID,t2.PARENT_ID from tree t1,tree t2 where t1.PARENT_ID=t2.CHILD_ID and t1.PARTNAME=@name
while exists(select * from tmp t1,tree t2 where t1.PARENT_ID=t2.CHILD_ID and t2.PARTNAME not in(select PARTNAME from tmp))
begin
insert into tmp
select distinct t2.PARTNAME, t1.QTY*t2.QTY,t2.CHILD_ID,t2.PARENT_ID from tmp t1,tree t2 where t1.PARENT_ID=t2.CHILD_ID and t2.PARTNAME not in(select PARTNAME from tmp)
end;
select PARTNAME,QTY,CHILD_ID from tmp where PARENT_ID=0
drop table tmp