给你一个类似的例子:(我找不到是哪个帖了) select datepart(hour,convert(datetime,getdate(),114))---24hour 数据表结构如下: 部门ID,部门名称,上级部门ID, 备注现在删除部门中的一条记录的话,要把它的下级部门都删除,也就是删除一个树状结构,请问各位高手,这个SQL如何写?create table dept (部门ID int,部门名称 varchar(30),上级部门ID varchar(30),备注 varchar(30)) go insert into dept values(11,'aaa',10,'') insert into dept values(21,'bbb',11,'') insert into dept values(31,'ccc',21,'') insert into dept values(41,'ddd',31,'') insert into dept values(51,'eee',41,'') insert into dept values(61,'fff',51,'') insert into dept values(71,'ggg',61,'') drop table #tempselect 部门id into #temp from dept where 部门ID=31 while exists (select 部门id from dept where 部门id not in (select * from #temp) and 上级部门id in (select * from #temp)) insert into #temp select 部门id from dept where 上级部门ID in (select * from #temp) and 部门id not in (select * from #temp) select * from #temp delete from dept where 部门id in (select * from #temp)or declare @Id1 int declare @Id2 int declare cr cursor for select 部门id,上级部门id from dept open cr select 部门id into #temp from dept where 部门ID=31---your id fetch next from cr into @id1,@id2 WHILE @@FETCH_STATUS = 0 BEGIN if @id2 IN (select * from #temp) and @id1 not in (select * from #temp) insert into #temp values(@id1) fetch next from cr into @id1,@id2 END CLOSE cr DEALLOCATE cr select * from #temp GO delete from dept where 部门id in (select * from #temp)
select datepart(hour,convert(datetime,getdate(),114))---24hour
数据表结构如下:
部门ID,部门名称,上级部门ID, 备注现在删除部门中的一条记录的话,要把它的下级部门都删除,也就是删除一个树状结构,请问各位高手,这个SQL如何写?create table dept (部门ID int,部门名称 varchar(30),上级部门ID varchar(30),备注 varchar(30))
go
insert into dept values(11,'aaa',10,'')
insert into dept values(21,'bbb',11,'')
insert into dept values(31,'ccc',21,'')
insert into dept values(41,'ddd',31,'')
insert into dept values(51,'eee',41,'')
insert into dept values(61,'fff',51,'')
insert into dept values(71,'ggg',61,'')
drop table #tempselect 部门id into #temp from dept where 部门ID=31
while exists (select 部门id from dept where 部门id not in (select * from #temp)
and 上级部门id in (select * from #temp))
insert into #temp
select 部门id from dept where 上级部门ID in (select * from #temp)
and 部门id not in (select * from #temp)
select * from #temp
delete from dept where 部门id in (select * from #temp)or declare @Id1 int
declare @Id2 int
declare cr cursor for select 部门id,上级部门id from dept
open cr
select 部门id into #temp from dept where 部门ID=31---your id
fetch next from cr into @id1,@id2
WHILE @@FETCH_STATUS = 0
BEGIN
if @id2 IN (select * from #temp) and @id1 not in (select * from #temp)
insert into #temp values(@id1)
fetch next from cr into @id1,@id2
END
CLOSE cr
DEALLOCATE cr
select * from #temp
GO
delete from dept where 部门id in (select * from #temp)
我怎么看不出这个例子和我的问题有什么类似之处呢?
ALL:
这个问题用存储过程的话。我当然有办法做出来。
只是这个表我要的结果是我的一个视图中的一部分。这个视图还有很多别的内容是从别的表中UNION的。
如果我用存储过程求出了我要的东西,我不太可能把它显示在我的视图里了吧?
ODF端子表里记录每个端子和一条光路连接的记录
端子跳接表中记录每两个端子连接的记录”我一直搞不清你到底想实现什么?而现在可利用的条件是什么?
谢谢各位