如果哪位朋友想出了好的算法,可以QQ给我,多谢!我的工作QQ号是:16813027!希望能够与你共同交流经验!
解决方案 »
- 如何寫出這樣需求的sql?
- 求一个分页代码
- access 里面不能用嵌套的 distinct 求解决办法
- **100**急求SQL语句,如何能将4张表中的内容同时显示出来~谢谢~急!在线等**
- 一张表中两列需要排序
- 网站运行一至二天后就打不开网页了,重启又正常了,这是什么问题?
- sql语句里怎么限制小数位数啊 ?
- 极度困惑!请一定要帮忙!多谢!在线等待中!
- 今天应聘听到一的个问题,请教各位.
- vfp如何从文本中搜索出我指定的一段字符串?指针的问题!谢谢!
- 请教高手,sql server 7中关于图像存储用image类型的字段的问题,多谢!
- 有谁知道RTP/RTCP(实时传输协议/实时传输控制协议)?
以下版本可以用临时表或游标来做.
代碼編寫中……
create proc DeleteId
@Id int
ascreate Table #Table(id int)insert #Table select @Idwhile exists
(select id from tableType
where fid in (select id from #table)
and id not in (select id from #table)
)insert #Table(id)
select id from tableType
where fid in (select id from #table)
and id not in (select id from #table)delete tableType
from tableType b,#table t
where t.id=b.iddrop table #Tablego
可以用下面的代碼就可以達到目的declare @id int,@pid int,@tmp int
set @id=7 --測試數據 要刪除類別為7的資料
select @pid=parentid from type where typeid=@id
delete from type where typeid=@id --類別table
delete from question where typeid=@id --問題tablewhile @pid<>0
begin
select @tmp=isnull(parentid,0),@id=typeid from type where typeid=@pid
set @pid=@tmp
delete from type where typeid=@id
delete from question where typeid=@id
end
Create table Type(typeid int,parentid int)
問題表question的結構
create table question(QID int,typeid int)
測試數據
insert into type values (1,0)
insert into type values (2,0)
insert into type values (3,1)
insert into type values (4,2)
insert into type values (5,3)
insert into type values (6,3)
insert into type values (7,5)
insert into type values (8,3)
insert into question values (1,1)
insert into question values (2,2)
insert into question values (3,4)
insert into question values (4,1)
此問題不須用遞歸,因為可以一層一層處理類別,把每一層擁有子類別的ID都插進一個temp table,直到沒有子類別為止。然後再把temp table里的ID INNER JOIN 類別表,一次性刪除。比方說,3這個類別,有4個子類,然後這4個子類又分別有2個子類,這2個子又分別有一個子類,那麼總共就是有4層,程式只須跑4次Loop就可以得到結果。程式如下:(要借助temp table來實現)
本程式在MS SQLServer 2000中通過。
Declare @cnt int --在某一層時擁有子類的類別的個數
Declare @ID int
set @ID=3 --要刪除類別為3的資料及所有的子類SELECT @cnt=COUNT(*) From Type WHERE Parentid=@ID
Create table #tmp (ID int)
INSERT INTO #tmp Select TypeID From type where parentid=@IDWHILE @cnt>0 --找出所有子類的typeID, 插入temp table中
begin
INSERT INTO #tmp SELECT a.TypeID From Type a INNER JOIN #tmp b ON a.ParentID=b.ID AND a.TypeID NOT IN (SELECT ID From #tmp)
SELECT @cnt=COUNT(*) From Type a INNER JOIN #tmp ON a.ParentID=#tmp.ID AND a.TypeID NOT IN (SELECT ID From #tmp)
endDELETE Type From Type INNER JOIN #tmp ON type.TypeID=#tmp.ID
DELETE Question From Question INNER JOIN #tmp ON Question.Typeid=#tmp.IDdrop table #tmp
我假设type表有一个临时字段tmp,当然如果没有也可以建立临时表和连接实现,这里只讨论算法。则type表的3个字段分别为id,fid,tmp,以下是实现的存储过程。
*******************
CREATE PROCEDURE KILLALL
@id int
AS
declare @i int
--初始化tmp字段
update type set tmp = 0
--置上第一级节点的标志
update type set tmp = 1 where id = @id
set @i = 1
--循环置上所有子节点的标志
while exists(select * from type where tmp = @i)
begin
update type set tmp = @i + 1 where fid in (select id from type where tmp = @i)
set @i = @i + 1
end
--删除指定的节点及所有子孙节点
delete from type where tmp > 0
建立一个触发器如下:
Create trigger Trg_Type
ON Type
FOR DELETE
AS
Begin
Declare @Rows numeric(9,0),@RowsCount numeric(9,0)
Set @Rows=@@RowCount
Set @RowsCount=@@RowCount
while @Rows>0
begin
delete test where 类别ID in(select 类别ID from test where 父类别ID > 0 and 父类别ID not in(Select 类别ID from Test))
Set @Rows=@@RowCount
Set @RowsCount = @RowsCount + @Rows
end
delete 问题表 where 类别ID not in(select 类别ID from type)
Print '表Type影响的行数为' + cast(@rowscount as varchar) + '表问题表影响的行数为' + cast(@@rowcount as varchar)
End
此触发器在SqlServer7。0中验证通过
只要你Delete 任何一行那么它的子节点都会被删除!
谢谢!!