--可以,2005 ;with ach as ( select id,parentid from tb where id = 1 union all select a.id,a.parentid from tb a join ach b on a.parentid = b.id )select name from tb where id in (select id from ach)
-- 使用函数的方法:--建立 演示环境if object_id('tb_bookInfo') is not null drop table tb_bookInfo go create table tb_bookInfo(number int,name varchar(10),type int) insert tb_bookInfo select 1 ,'n1', 6 union all select 2 ,'n2', 3 if object_id('tb_bookType') is not null drop table tb_bookType go create table tb_bookType(id int,typeName varchar(10),parentid int) insert tb_bookType select 1,'英语',0 union all select 2,'生物',0 union all select 3,'计算机',0 union all select 4,'口语',1 union all select 5,'听力',1 union all select 6,'数据库',3 union all select 7,'软件工程',3 union all select 8,'SQL Server',6select a.*,b.level from tb_bookInfo a,f_getC(3) b where a.type=b.id order by b.level /* number name type level ----------- ---------- ----------- ----------- 2 n2 3 0 1 n1 6 1(所影响的行数为 2 行) */ --查所有父结点 if object_id('f_getP') is not null drop function f_getP go create function f_getP(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.parentid,@l from tb_bookType a,@re b where a.id=b.id and b.level=@l-1 and a.parentid<>0 end update @re set level=@l-level return end go --查所有子结点 if object_id('f_getC') is not null drop function f_getC go create function f_getC(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.id,@l from tb_bookType as a,@re as b where b.id=a.parentid and b.level=@l-1 end return end go --查所有父子结点 if object_id('f_getAll') is not null drop function f_getAll go create function f_getAll(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.parentid,@l from tb_bookType a,@re b where a.id=b.id and b.level=@l-1 and a.parentid<>0 end update @re set level=@l-level while @@rowcount>0 begin set @l=@l+1 insert @re select a.id,@l from tb_bookType as a,@re as b where b.id=a.parentid and b.level=@l-1 end return end go --删除演示drop table tb_bookInfodrop table tb_bookTypedrop function f_getPdrop function f_getC drop function f_getAll GO--sqlserver2005的新方法-- 建立演示环境 IF OBJECT_ID('[Dept]') IS NOT NULL DROP TABLE [Dept] GO CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20)) INSERT Dept SELECT 1, 0, N'财务部' UNION ALL SELECT 2, 0, N'行政部' UNION ALL SELECT 3, 0, N'业务部' UNION ALL SELECT 4, 0, N'业务部' UNION ALL SELECT 5, 4, N'销售部' UNION ALL SELECT 6, 4, N'MIS' UNION ALL SELECT 7, 6, N'UI' UNION ALL SELECT 8, 6, N'软件开发' UNION ALL SELECT 9, 8, N'内部开发' GO --1、父-〉子 -- 查询指定部门下面的所有部门 DECLARE @Dept_name nvarchar(20) SET @Dept_name = N'MIS' ;WITH DEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id ) SELECT * FROM DEPTS GO --结果如下 /* id parent_id name ----------- ----------- -------------------- 6 4 MIS 7 6 UI 8 6 软件开发 9 8 内部开发(所影响的行数为 4 行) */--2、子-〉父 -- 查询指定部门下面的所有部门 DECLARE @Dept_name nvarchar(20) SET @Dept_name = N'内部开发' ;WITH DEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name --SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent FROM Dept where @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id ) SELECT * FROM DEPTS GO--结果如下 /* id parent_id name ----------- ----------- -------------------- 9 8 内部开发 8 6 软件开发 6 4 MIS 4 0 业务部(所影响的行数为 4 行) */-- 删除演示环境 DROP TABLE Dept
不用with cte 如何实现??
因为我是想往现成的sql语句上边加东西 只能拼接 where 子句 问题是 用在where 子句即是不用函数,不用with 能实现么
--可以,2005
;with ach as
(
select id,parentid from tb where id = 1
union all
select a.id,a.parentid
from tb a join ach b on a.parentid = b.id
)select name from tb where id in (select id from ach)
go
create table tb_bookInfo(number int,name varchar(10),type int)
insert tb_bookInfo
select 1 ,'n1', 6 union all
select 2 ,'n2', 3
if object_id('tb_bookType') is not null drop table tb_bookType
go
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英语',0 union all
select 2,'生物',0 union all
select 3,'计算机',0 union all
select 4,'口语',1 union all
select 5,'听力',1 union all
select 6,'数据库',3 union all
select 7,'软件工程',3 union all
select 8,'SQL Server',6select a.*,b.level from tb_bookInfo a,f_getC(3) b where a.type=b.id order by b.level
/*
number name type level
----------- ---------- ----------- -----------
2 n2 3 0
1 n1 6 1(所影响的行数为 2 行)
*/
--查所有父结点
if object_id('f_getP') is not null drop function f_getP
go
create function f_getP(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l from tb_bookType a,@re b
where a.id=b.id and b.level=@l-1 and a.parentid<>0
end
update @re set level=@l-level
return
end
go
--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l from tb_bookType as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
return
end
go --查所有父子结点
if object_id('f_getAll') is not null drop function f_getAll
go
create function f_getAll(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l from tb_bookType a,@re b
where a.id=b.id and b.level=@l-1 and a.parentid<>0
end
update @re set level=@l-level
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l from tb_bookType as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
return
end
go
--删除演示drop table tb_bookInfodrop table tb_bookTypedrop function f_getPdrop function f_getC
drop function f_getAll
GO--sqlserver2005的新方法-- 建立演示环境
IF OBJECT_ID('[Dept]') IS NOT NULL
DROP TABLE [Dept]
GO
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
--1、父-〉子
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO
--结果如下
/*
id parent_id name
----------- ----------- --------------------
6 4 MIS
7 6 UI
8 6 软件开发
9 8 内部开发(所影响的行数为 4 行)
*/--2、子-〉父
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'内部开发'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept WHERE name = @Dept_name
--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent FROM Dept where @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id
)
SELECT * FROM DEPTS
GO--结果如下
/*
id parent_id name
----------- ----------- --------------------
9 8 内部开发
8 6 软件开发
6 4 MIS
4 0 业务部(所影响的行数为 4 行)
*/-- 删除演示环境
DROP TABLE Dept
只能拼接 where 子句
问题是 用在where 子句即是不用函数,不用with
能实现么