select * from 部门表 where CodeID like '3%' and CodeID<>3
--如果编号没有这种规则,则: --创建处理函数 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 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 proc sp_aa (@codeID varchar(10)) as select * from 部門表 where ParentID=@CodeIDexec sp_aa '3'運行結果: TypeID CodeID Description ParentID ------ -------------------- -------------------- -------------------- UN 301 集團本部 3 UN 302 中國分公司 3(影響 2 個資料列)
--你用下面这个函数检测一下就知道了: --自定义函数--检测某个编码出发,是否被循环引用 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 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 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的值动态生成其下所有记录(即下属公司和下属公司的部门)吗
不行呀,会死循环,到现在还没执行完呢
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 proc sp_aa
(@codeID varchar(10))
as
select * from 部門表
where ParentID=@CodeIDexec sp_aa '3'運行結果:
TypeID CodeID Description ParentID
------ -------------------- -------------------- --------------------
UN 301 集團本部 3
UN 302 中國分公司 3(影響 2 個資料列)
你的语句不能实现根据CodeID的值生成其下所有记录(即下属公司和下属公司的部门),如果CodeID=3 得到的记录除了集团本部,中国分公司外还应有集团本部与中国分公司下属的各个部门
--自定义函数--检测某个编码出发,是否被循环引用
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] [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