sql 语句查询

Question

我现在有两个表数据分别是：
表Aid    name    typeId
--------------------
1      test      1
2      test2     2
3      test3     3
4      teset4    3表B  (是个级联目录表，parentId 对应的是自己的 id 即是自己的父目录，为0则为第一层)typeId     type     parentId
--------------------------
1      type       0
2      type2      1
3      type3      2现在两表连接，我想根据父目录查出所有子目录的该怎么查询。 注意：这里是多层目录。

Answer 1

create table #EnterPrise
(
  Department nvarchar(50),--部门名称
  ParentDept nvarchar(50),--上级部门
  DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department           ParentDept                DepartManage      rank
--------------- -------------------- ----------------------- -----------
技术部               总经办                    Tom               0
程序组               技术部                    GiGi              1
设计组               技术部                    yoyo              1
专项组               程序组                    Yue               2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department               ParentDept          DepartManage    rank
-------------------- ----------------------  -----------  -----------
程序组                   技术部                 GiGi           0
技术部                   总经办                 Tom            1
总经办                                          Boss           2
*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/31/5274571.aspx

Answer 2

create table #a(id int,name varchar(20),typeId int)insert  #a  select 1 ,'test', 1
insert  #a  select 2 ,'test2', 2
insert  #a  select 3 ,'test3', 3
insert  #a  select 4 ,'teset4', 3create table #b(typeId int, type varchar(10),parentId int)insert  #b  select 1 ,'type', 0
insert  #b  select 2 ,'type2', 1
insert  #b  select 3 ,'type3', 2
with cte as(select a.*,b.type,0 as levl from #a a,#b b 
where a.typeId=b.typeid 
and a.id=1  --?
union allselect a.*,b.type,levl+1  from #a a,#b b ,cte c
where a.typeId=b.typeid 
and b.parentid=c.id
)
select * from cte         id name                      typeId type              levl
----------- -------------------- ----------- ---------- -----------
          1 test                           1 type                 0
          2 test2                          2 type2                1
          3 test3                          3 type3                2
          4 teset4                         3 type3                2(4 行受影响)

Answer 3

if exists(select object_id('tb'))
drop table tb
create table tb(id varchar(3) , pid varchar(3) , name varchar(10)) 
insert into tb values('001' , null  , '广东省') 
insert into tb values('002' , '001' , '广州市') 
insert into tb values('003' , '001' , '深圳市') 
insert into tb values('004' , '002' , '天河区') 
insert into tb values('005' , '003' , '罗湖区') 
insert into tb values('006' , '003' , '福田区') 
insert into tb values('007' , '003' , '宝安区') 
insert into tb values('008' , '007' , '西乡镇') 
insert into tb values('009' , '007' , '龙华镇') 
insert into tb values('010' , '007' , '松岗镇') 
go --查询指定节点及其所有子节点的函数 
alter function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int) 
as 
begin 
  declare @level int 
  set @level = 1 
  insert into @t_level select @id , @level 
  while @@ROWCOUNT > 0 
  begin 
    set @level=@level+1
insert into @t_level 
select a.id,@level from tb a,@t_level b where a.pid=b.id and b.level=@level-1
  end 
  return 
end 
go
--调用函数查询001(广东省)及其所有子节点 
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id 
论坛里面很多的

Answer 4

with 做这种递归查询效率是最高的

Answer 5

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-03-30 09:29:56
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([id] INT,[name] NVARCHAR(10),[typeId] INT)
INSERT [a]
SELECT 1,'test',1 UNION ALL
SELECT 2,'test2',2 UNION ALL
SELECT 3,'test3',3 UNION ALL
SELECT 4,'teset4',3
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([typeId] INT,[type] NVARCHAR(10),[parentId] INT)
INSERT [b]
SELECT 1,'type',0 UNION ALL
SELECT 2,'type2',1 UNION ALL
SELECT 3,'type3',2
GO
--SELECT * FROM [b]-->SQL查询如下:;with t as
(
select a.*,b.parentId,lvl=0,px=cast(ID as varchar(1000)) from a join b on a.typeId=b.typeId
union all
select a.*,b.parentId,lvl=c.lvl+1,cast(c.px+LTRIM(a.ID) as varchar(1000)) 
from a 
join b on a.typeId=b.typeId
join t c on b.parentId=c.id
) 
select REPLICATE('.',lvl)+LTRIM(id) ID,name,typeId,parentId 
from t 
order by px
/*
ID name typeId parentId
1 test 1 0
.2 test2 2 1
..3 test3 3 2
..4 teset4 3 2
2 test2 2 1
.3 test3 3 2
.4 teset4 3 2
3 test3 3 2
4 teset4 3 2
*/