--测试--测试数据 create table 部门表(TypeID char(2),CodeID varchar(20),Description varchar(20),ParentID varchar(20)) insert 部门表 select 'UN','3','IBM集团总公司','UN' union all select 'UN','301','集团本部','3' union all select 'UN','302','中国分公司','3' union all select 'BM','30101','部门1','301' union all select 'BM','30102','部门2','301' union all select 'BM','30201','部门1','302' union all select 'BM','30202','部门2','302' go--编号有规则吗? 如果有,可以用: select * from 部门表 where CodeID like '3%' go--删除测试 drop table 部门表/*--测试结果 TypeID CodeID Description ParentID ------ -------------------- -------------------- -------------------- UN 3 IBM集团总公司 UN UN 301 集团本部 3 UN 302 中国分公司 3 BM 30101 部门1 301 BM 30102 部门2 301 BM 30201 部门1 302 BM 30202 部门2 302(所影响的行数为 7 行) --*/
--如果编号没有这种规则,则: --创建处理函数 create function f_child(@CodeID varchar(20)) returns @re table(CodeID varchar(20),level int) as begin declare @l int set @l=0 insert @re select @codeid,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.CodeID,@l from 部门表 a join @re b on a.ParentID=b.CodeID where b.level=@l-1 end return end go--调用实现查询 select a.* from 部门表 a join f_child('3') b on a.CodeID=b.CodeID go
--测试--测试数据 create table 部门表(TypeID char(2),CodeID varchar(20),Description varchar(20),ParentID varchar(20)) insert 部门表 select 'UN','3','IBM集团总公司','UN' union all select 'UN','301','集团本部','3' union all select 'UN','302','中国分公司','3' union all select 'BM','30101','部门1','301' union all select 'BM','30102','部门2','301' union all select 'BM','30201','部门1','302' union all select 'BM','30202','部门2','302' go--如果编号没有这种规则,则: --创建处理函数 create function f_child(@CodeID varchar(20)) returns @re table(CodeID varchar(20),level int) as begin declare @l int set @l=0 insert @re select @codeid,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.CodeID,@l from 部门表 a join @re b on a.ParentID=b.CodeID where b.level=@l-1 end return end go--调用实现查询 select a.* from 部门表 a join f_child('3') b on a.CodeID=b.CodeID go--删除测试 drop table 部门表 drop function f_child/*--测试结果 TypeID CodeID Description ParentID ------ -------------------- -------------------- -------------------- UN 3 IBM集团总公司 UN UN 301 集团本部 3 UN 302 中国分公司 3 BM 30101 部门1 301 BM 30102 部门2 301 BM 30201 部门1 302 BM 30202 部门2 302(所影响的行数为 7 行) --*/
--你用下面这个函数检测一下就知道了: --自定义函数--检测某个编码出发,是否被循环引用 create function f_chkid(@CodeID varchar(20)) returns bit --循环,返回1,否则返回0 as begin declare @re bit,@ParentID varchar(20) set @re=0 --检测 select @ParentID=ParentID from 部门表 where CodeID=@CodeID while @@rowcount>0 begin if @ParentID=@CodeID begin set @re=1 goto lbErr end select @ParentID=ParentID from 部门表 where CodeID=@ParentID endlbErr: return(@re) end go--显示表中的那些数据不符合规范 select * from 部门表 a where dbo.f_chkid(CodeID)=1 go
--你自己写错字段,提问时的字段,和这次给出的字段都不对应,当然就出不了结果 --按这次提供的字段修改的函数:--自定义函数--检测某个编码出发,是否被循环引用 create function f_chkid(@code varchar(20)) returns bit --循环,返回1,否则返回0 as begin declare @re bit,@ParentID varchar(20) set @re=0 --检测 select @ParentID=ParentID from 部门表 where code=@code while @@rowcount>0 begin if @ParentID=@code begin set @re=1 goto lbErr end select @ParentID=ParentID from 部门表 where CodeID=@ParentID endlbErr: return(@re) end go
--得到指定Code 的所有下属的函数create function f_child(@Code varchar(20)) returns @re table(Code varchar(20),level int) as begin declare @l int set @l=0 insert @re select @code,@l while @@rowcount>0 and @l<5 begin set @l=@l+1 insert @re select a.Code,@l from 部门表 a join @re b on a.ParentID=b.Code where b.level=@l-1 end return end go
TO:zjcxc(: 邹建 :) 我按你给的函数: 部门表为: Depart create function f_child(@Code varchar(20)) returns @re table(Code varchar(20),level int) as begin declare @l int set @l=0 insert @re select @code,@l while @@rowcount>0 and @l<5 begin set @l=@l+1 insert @re select a.Code,@l from Depart a join @re b on a.ParentID=b.Code where b.level=@l-1 end return end go建完函数后按下列语句执行: select a.* from Depart a join f_child('3') b on a.CodeID=b.CodeID go但出现错误: 列名 'CodeID' 无效。
表结构:CREATE TABLE [Depart] ( [CodeId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL , [level] [smallint] NOT NULL , [code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [Description] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL , [ParentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [CPtr] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO表数据: insert into Depart Values ('UN',1,'3','电力集团','UN','1') insert into Depart Values ('UN',2,'301','集团本部','3','1') insert into Depart Values ('BM',3,'30101','总办','301','0') insert into Depart Values ('BM',3,'30102','车队','301','0') insert into Depart Values ('UN',2,'303','漳州分公司','3','1') insert into Depart Values ('UN',2,'304','上海分公司','3','1') insert into Depart Values ('BM',3,'30301','董事长室','303','0') insert into Depart Values ('BM',3,'30302','办公室','303','0') insert into Depart Values ('BM',3,'30401','五矿部','304','0') insert into Depart Values ('BM',3','30402','科技信息部','304','0').......
CREATE TABLE [Depart] ( [CodeId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL , [level] [smallint] NOT NULL , [code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [Description] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL , [ParentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [CPtr] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO insert into Depart Values ('UN',1,'3','电力集团','UN','1') insert into Depart Values ('UN',2,'301','集团本部','3','1') insert into Depart Values ('BM',3,'30101','总办','301','0') insert into Depart Values ('BM',3,'30102','车队','301','0') insert into Depart Values ('UN',2,'303','漳州分公司','3','1') insert into Depart Values ('UN',2,'304','上海分公司','3','1') insert into Depart Values ('BM',3,'30301','董事长室','303','0') insert into Depart Values ('BM',3,'30302','办公室','303','0') insert into Depart Values ('BM',3,'30401','五矿部','304','0') insert into Depart Values ('BM',3,'30402','科技信息部','304','0') go--处理函数 create function f_child(@Code varchar(20)) returns @re table(Code varchar(20),level int) as begin declare @l int set @l=0 insert @re select @code,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.Code,@l from Depart a join @re b on a.ParentID=b.Code where b.level=@l-1 end return end goselect a.* from Depart a join f_child('3') b on a.Code=b.Code go--删除测试 drop table depart drop function f_child
create table 部门表(TypeID char(2),CodeID varchar(20),Description varchar(20),ParentID varchar(20))
insert 部门表 select 'UN','3','IBM集团总公司','UN'
union all select 'UN','301','集团本部','3'
union all select 'UN','302','中国分公司','3'
union all select 'BM','30101','部门1','301'
union all select 'BM','30102','部门2','301'
union all select 'BM','30201','部门1','302'
union all select 'BM','30202','部门2','302'
go--编号有规则吗? 如果有,可以用:
select * from 部门表 where CodeID like '3%'
go--删除测试
drop table 部门表/*--测试结果
TypeID CodeID Description ParentID
------ -------------------- -------------------- --------------------
UN 3 IBM集团总公司 UN
UN 301 集团本部 3
UN 302 中国分公司 3
BM 30101 部门1 301
BM 30102 部门2 301
BM 30201 部门1 302
BM 30202 部门2 302(所影响的行数为 7 行)
--*/
--创建处理函数
create function f_child(@CodeID varchar(20))
returns @re table(CodeID varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select @codeid,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.CodeID,@l
from 部门表 a join @re b on a.ParentID=b.CodeID
where b.level=@l-1
end
return
end
go--调用实现查询
select a.*
from 部门表 a
join f_child('3') b on a.CodeID=b.CodeID
go
create table 部门表(TypeID char(2),CodeID varchar(20),Description varchar(20),ParentID varchar(20))
insert 部门表 select 'UN','3','IBM集团总公司','UN'
union all select 'UN','301','集团本部','3'
union all select 'UN','302','中国分公司','3'
union all select 'BM','30101','部门1','301'
union all select 'BM','30102','部门2','301'
union all select 'BM','30201','部门1','302'
union all select 'BM','30202','部门2','302'
go--如果编号没有这种规则,则:
--创建处理函数
create function f_child(@CodeID varchar(20))
returns @re table(CodeID varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select @codeid,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.CodeID,@l
from 部门表 a join @re b on a.ParentID=b.CodeID
where b.level=@l-1
end
return
end
go--调用实现查询
select a.*
from 部门表 a
join f_child('3') b on a.CodeID=b.CodeID
go--删除测试
drop table 部门表
drop function f_child/*--测试结果
TypeID CodeID Description ParentID
------ -------------------- -------------------- --------------------
UN 3 IBM集团总公司 UN
UN 301 集团本部 3
UN 302 中国分公司 3
BM 30101 部门1 301
BM 30102 部门2 301
BM 30201 部门1 302
BM 30202 部门2 302(所影响的行数为 7 行)
--*/
这个语句是要根据CodeID的值来动态生成其下属所有记录(即下属公司和下属公司的部门),再帮帮忙
--自定义函数--检测某个编码出发,是否被循环引用
create function f_chkid(@CodeID varchar(20))
returns bit --循环,返回1,否则返回0
as
begin
declare @re bit,@ParentID varchar(20)
set @re=0 --检测
select @ParentID=ParentID from 部门表 where CodeID=@CodeID
while @@rowcount>0
begin
if @ParentID=@CodeID
begin
set @re=1
goto lbErr
end
select @ParentID=ParentID from 部门表 where CodeID=@ParentID
endlbErr:
return(@re)
end
go--显示表中的那些数据不符合规范
select * from 部门表 a
where dbo.f_chkid(CodeID)=1
go
CodeId level code description ParentID Ctr
UN 1 3 IBM集团 UN 1
UN 2 301 IBM中国分公司 3 1
BM 3 30101 部门1 301 0
BM 3 30102 部门2 301 0
UN 2 303 IBM德国分公司 3 1
UN 2 309 IBM法国分公司 3 1
BM 3 30301 科技信息部 303 0
BM 3 30302 轻工部 303 0
BM 3 30901 董事长室 309 0
BM 3 30902 总工会办公室 309 0
注:Ctr我也不知道是什么字段,但很象是"是否有子记录"字段,由于是别的公司的库发过来我们要进行二次开发,他们也没讲清楚谢谢你希望能给点指点帮帮忙
执行: f_chkid()
我测试了一下,没有数据显示出来
--按这次提供的字段修改的函数:--自定义函数--检测某个编码出发,是否被循环引用
create function f_chkid(@code varchar(20))
returns bit --循环,返回1,否则返回0
as
begin
declare @re bit,@ParentID varchar(20)
set @re=0 --检测
select @ParentID=ParentID from 部门表 where code=@code
while @@rowcount>0
begin
if @ParentID=@code
begin
set @re=1
goto lbErr
end
select @ParentID=ParentID from 部门表 where CodeID=@ParentID
endlbErr:
return(@re)
end
go
returns @re table(Code varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select @code,@l
while @@rowcount>0 and @l<5
begin
set @l=@l+1
insert @re select a.Code,@l
from 部门表 a join @re b on a.ParentID=b.Code
where b.level=@l-1
end
return
end
go
我按你给的函数:
部门表为: Depart
create function f_child(@Code varchar(20))
returns @re table(Code varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select @code,@l
while @@rowcount>0 and @l<5
begin
set @l=@l+1
insert @re select a.Code,@l
from Depart a join @re b on a.ParentID=b.Code
where b.level=@l-1
end
return
end
go建完函数后按下列语句执行:
select a.*
from Depart a
join f_child('3') b on a.CodeID=b.CodeID
go但出现错误:
列名 'CodeID' 无效。
[CodeId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[level] [smallint] NOT NULL ,
[code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Description] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
[ParentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CPtr] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO表数据:
insert into Depart Values ('UN',1,'3','电力集团','UN','1')
insert into Depart Values ('UN',2,'301','集团本部','3','1')
insert into Depart Values ('BM',3,'30101','总办','301','0')
insert into Depart Values ('BM',3,'30102','车队','301','0')
insert into Depart Values ('UN',2,'303','漳州分公司','3','1')
insert into Depart Values ('UN',2,'304','上海分公司','3','1')
insert into Depart Values ('BM',3,'30301','董事长室','303','0')
insert into Depart Values ('BM',3,'30302','办公室','303','0')
insert into Depart Values ('BM',3,'30401','五矿部','304','0')
insert into Depart Values ('BM',3','30402','科技信息部','304','0').......
[CodeId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[level] [smallint] NOT NULL ,
[code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Description] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
[ParentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CPtr] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into Depart Values ('UN',1,'3','电力集团','UN','1')
insert into Depart Values ('UN',2,'301','集团本部','3','1')
insert into Depart Values ('BM',3,'30101','总办','301','0')
insert into Depart Values ('BM',3,'30102','车队','301','0')
insert into Depart Values ('UN',2,'303','漳州分公司','3','1')
insert into Depart Values ('UN',2,'304','上海分公司','3','1')
insert into Depart Values ('BM',3,'30301','董事长室','303','0')
insert into Depart Values ('BM',3,'30302','办公室','303','0')
insert into Depart Values ('BM',3,'30401','五矿部','304','0')
insert into Depart Values ('BM',3,'30402','科技信息部','304','0')
go--处理函数
create function f_child(@Code varchar(20))
returns @re table(Code varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select @code,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.Code,@l
from Depart a join @re b on a.ParentID=b.Code
where b.level=@l-1
end
return
end
goselect a.*
from Depart a join f_child('3') b on a.Code=b.Code
go--删除测试
drop table depart
drop function f_child