问一条SQl语句实现
如何用SQl语句实现查询一个部门下的所有员工?
表结构如下: 部门表 Department 字段( ID ,DepartmentName , ParentDepartment)
员工表 Employee 字段( ID ,EmployeeName , ParentID )
因为部门下面又有子部门，所以要查出这个部门下及其子部门下的所有员工,..
只用SQL实现如何实现?
[解决办法]
--生成测试数据create table BOM(ID INT,PID INT,MSG VARCHAR(1000))insert into BOM select 1,0,NULLinsert into BOM select 2,1,NULLinsert into BOM select 3,1,NULLinsert into BOM select 4,2,NULLinsert into BOM select 5,3,NULLinsert into BOM select 6,5,NULLinsert into BOM select 7,6,NULLgo--创建用户定义函数用于取每个父节点下子节点的采购配置信息create function f_getChild(@ID VARCHAR(10))returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)asbegin declare @i int set @i = 1 insert into @t select ID,PID,@i from BOM where PID = @ID while @@rowcount<>0 begin set @i = @i + 1 insert into @t select a.ID,a.PID,@i from BOM a,@t b where a.PID=b.ID and b.Level = @i-1 end returnendgo--执行查询select ID from dbo.f_getChild(3)go--输出结果/*ID----567*/--删除测试数据drop function f_getChilddrop table BOM
[解决办法]
部门表 Department 字段( ID ,DepartmentName , ParentDepartment)
员工表 Employee 字段( ID ,EmployeeName , ParentID )
create function wsp(@ID VARCHAR(10))returns varchar(500)asbegin declare @i int declare @t table(ID int,ParentDepartment int,Level INT) set @i = 1 insert into @t select ID,ParentDepartment,@i from Department where ID = @ID while @@rowcount<>0 begin set @i = @i + 1 insert into @t select a.ID,a.ParentDepartment,@i from Department a,@t b where a.ParentDepartment=b.ID and b.Level = @i-1 end declare @sql varchar(500) select @sql=isnull(@sql+',','')+ltrim(id) from @t return @sqlendgo--调用：select * from Employee where charindex(','+ltrim(ParentID)+',',','+dbo.wsp(部门id)+',')>0
[解决办法]
--测试数据CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))INSERT tb SELECT '001',NULL ,'山东省'UNION ALL SELECT '002','001','烟台市'UNION ALL SELECT '004','002','招远市'UNION ALL SELECT '003','001','青岛市'UNION ALL SELECT '005',NULL ,'四会市'UNION ALL SELECT '006','005','清远市'UNION ALL SELECT '007','006','小分市'GO--查询指定节点及其所有子节点的函数CREATE FUNCTION f_Cid(@ID char(3))RETURNS @t_Level TABLE(ID char(3),Level int)ASBEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END RETURNENDGO--调用函数查询002及其所有子节点SELECT a.*FROM tb a,f_Cid('002') bWHERE a.ID=b.ID/*--结果ID PID Name ------ ------- ---------- 002 001 烟台市004 002 招远市--*/
[解决办法]
create table tb(id int, name varchar(10), pid int, px int)insert into tb values(0 , '栏目分类', 0 , 1)insert into tb values(1 , '动物' , 0 , 1) insert into tb values(2 , '视频' , 0 , 2) insert into tb values(3 , '老虎' , 1 , 1) insert into tb values(4 , '狮子' , 1 , 2) insert into tb values(5 , '搞笑' , 2 , 1) go--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @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 --调用函数查询id = 1及其所有子节点 SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID /*id name pid px ----------- ---------- ----------- ----------- 1 动物 0 13 老虎 1 14 狮子 1 2（所影响的行数为 3 行）*/drop table tbdrop function dbo.f_cid
[解决办法]
Create Table department(departmenid Int,parentid Int)Insert department Select 60, nullUnion All Select 1, 0Union All Select 2, 1Union All Select 3, 1Union All Select 4, 2Union All Select 5, 2Union All Select 6, 3Union All Select 7, 3Union All Select 8, 7GO--建立函數Create Function F_GetParent(@departmenid Int)Returns @Tree Table (departmenid Int, parentid Int)AsBeginInsert @Tree Select * From department Where departmenid = @departmenidWhile @@Rowcount > 0Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not NullReturnEndGO--測試Select departmenid From dbo.F_GetParent(8) Order By parentidGO--刪除測試環境Drop Table departmentDrop Function F_GetParent