create table #aa(a int) insert #aa values(1)while exists(select 1 from 表 where deptid in (select a from #aa)) begin delete 表 where deptid in (select a from #aa) insert #aa select 表 where parentid in (select a from #aa) end
to everyone 提醒大家 子部门可能还有子部门 深度不定,尽量不使用存储过程
http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.8570978-------------- 参考:--查询ID为1的所有下层记录declare @aa varchar(8000) set @aa=dbo.FN_32GetSubClass(1,default,default) print @aa exec('delete treeclass where TC_id in ('+ @aa +')')方法二, delete treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_32getsubclass(1,default,default))>0
declare @temp table (id char(8))insert @temp values ('1')while exists (select deptid from Tab where parentid in (select id from @temp) and deptid not in (select id from @temp) ) insert @temp select deptid from Tab where parentid in (select id from @temp) and deptid not in (select id from @temp)delete Tab where deptid in (select id from @temp)
假设要删除的部门编号是:1delete from 表 where deptid=1 or parentid=1
to StudyAll(敢于浮沙筑高台) 应为深度不定,在SQLSERVER里,只有用过程,我写的是对的!你没试试吗?有错吗?
delete from 表 where deptid in (delID,select DeptID from 表 Where parentID=delID)
delete from 表 where deptid in (select DeptID from 表 Where parentID=delID) or deptid=delID
忘了看你的深度要求了。 对于这样的问题,最好的办法是通过触发器来实现: Create Triger DelDept On 表 For Delete As delete from 表 where parentid in ( select deptid from Deleted)
insert #aa values(1)while exists(select 1 from 表 where deptid in (select a from #aa))
begin
delete 表 where deptid in (select a from #aa)
insert #aa select 表 where parentid in (select a from #aa)
end
提醒大家 子部门可能还有子部门 深度不定,尽量不使用存储过程
参考:--查询ID为1的所有下层记录declare @aa varchar(8000)
set @aa=dbo.FN_32GetSubClass(1,default,default)
print @aa
exec('delete treeclass where TC_id in ('+ @aa +')')方法二,
delete treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_32getsubclass(1,default,default))>0
where parentid in (select id from @temp)
and deptid not in (select id from @temp)
)
insert @temp
select deptid from Tab
where parentid in (select id from @temp)
and deptid not in (select id from @temp)delete Tab
where deptid in (select id from @temp)
应为深度不定,在SQLSERVER里,只有用过程,我写的是对的!你没试试吗?有错吗?
对于这样的问题,最好的办法是通过触发器来实现:
Create Triger DelDept On 表
For Delete
As
delete from 表 where parentid in ( select deptid from Deleted)