-->Title:Generating test data -->Author:wufeng4552 -->Date :2009-09-30 14:27:56
if not object_id('tb') is null drop table tb Go Create table tb([id] int,[parentid] int,[name] varchar(10)) Insert tb select 1,0,null union all select 2,0,null union all select 3,1,null union all select 4,3,null union all select 5,2,null Go -->Title:查找指定節點下的子結點 if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID go create function Uf_GetChildID(@ParentID int) returns @t table(ID int) as begin insert @t select ID from tb where ParentID=@ParentID while @@rowcount<>0 begin insert @t select a.ID from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end return end go select * from dbo.Uf_GetChildID(0)t where id not in(select [parentid] from tb) /* ID ----------- 5 4 */
/*--------------------------------- -- Author : htl258(Tony) -- Date : 2009-09-30 14:34:04 -- Version: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)---------------------------------*/ --> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([id] int,[parentid] int,[name] varchar(10)) Insert [tb] Select 1,0,null union all Select 2,0,null union all Select 3,1,null union all Select 4,3,null union all Select 5,2,null Go --Select * from [tb]-->SQL查询如下: ;with t as ( select * from tb where parentid=0 union all select a.* from tb a,t b where a.parentid=b.id ) select id from t a where not exists( select 1 from t where a.id=parentid) /* id ----------- 5 4(2 行受影响) */
;with t as ( select * ,0 as [end] from ta a where parentid = 0 union all select a.* ,case when exists(select 1 from ta where parentid = a.id) then 0 else 1 end from ta a ,t where a.parentid = t.id) select * from t where [end]= 1
if not object_id('tb') is null drop table tb Go Create table tb([id] int,[parentid] int,[name] varchar(10)) Insert tb select 1,0,null union all select 2,0,null union all select 3,1,null union all select 4,3,null union all select 5,2,null Go -->Title:查找指定節點下的子結點 if object_id('fn_GetChildID')is not null drop function fn_GetChildID go create function fn_GetChildID(@ParentID int) returns @t table(ID int,PID int) as begin insert @t select ID,[parentid] from tb where ParentID=@ParentID while @@rowcount>0 begin insert @t select a.ID,a.[parentid] from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end delete a from @t a where exists(select 1 from @t where a.id=pid) return end go select ID from dbo.fn_GetChildID(0) /* ID ----------- 5 4(2 行受影响) */用7L的改一下看是不是你要的.
偶也来凑个热闹,用函数实现直接选出子节点 if not object_id('tb') is null drop table tb IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU Go CREATE FUNCTION FUN_MU(@ID INT,@FID INT) RETURNS BIT AS BEGIN DECLARE @TEMPID INT SELECT @TEMPID=PARENTID FROM TB WHERE ID=@ID WHILE @TEMPID<>@FID AND @TEMPID IS NOT NULL BEGIN SELECT @TEMPID=PARENTID FROM TB WHERE ID=@TEMPID END IF @TEMPID=@FID AND NOT EXISTS(SELECT 1 FROM TB WHERE PARENTID=@ID) RETURN 1 RETURN 0 END GO Create table tb([id] int,[parentid] int,[name] varchar(10)) Insert tb select 1,0,null union all select 2,0,null union all select 3,1,null union all select 4,3,null union all select 5,2,null GoSELECT * FROM TB WHERE DBO.FUN_MU(ID,0)=1/* 4 3 NULL 5 2 NULL */
MS我的也有问题,小改一下下: if not object_id('tb') is null drop table tb IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU Go CREATE FUNCTION FUN_MU(@ID INT,@FID INT) RETURNS BIT AS BEGIN DECLARE @TEMPID INT SELECT @TEMPID=PARENTID FROM TB WHERE ID=@ID WHILE @TEMPID<>@FID AND @TEMPID IS NOT NULL BEGIN IF EXISTS (SELECT PARENTID FROM TB WHERE ID=@TEMPID) SELECT @TEMPID=PARENTID FROM TB WHERE ID=@TEMPID ELSE SELECT @TEMPID=NULL END IF @TEMPID=@FID AND NOT EXISTS(SELECT 1 FROM TB WHERE PARENTID=@ID) RETURN 1 RETURN 0 END GO Create table tb([id] int,[parentid] int,[name] varchar(10)) Insert tb SELECT 1, 0 ,'体育用品' UNION ALL SELECT 2, 0 ,'户外运动' UNION ALL SELECT 3, 1 ,'篮球' UNION ALL SELECT 4, 1 ,'足球' UNION ALL SELECT 5, 2 ,'帐篷' UNION ALL SELECT 6, 2 ,'登山鞋' UNION ALL SELECT 7, 0 ,'男士用品' UNION ALL SELECT 8, 7 ,'刮胡刀' UNION ALL SELECT 9, 3 ,'大号篮球' GoSELECT * FROM TB WHERE DBO.FUN_MU(ID,1)=1/* 4 1 足球 9 3 大号篮球 */
我把你的和7楼高手综合了下 删除语句改成7楼的条件 delete from @t where id in(select [parentid] from tb)这个语句功能效率如何?
delete t from @t t where exists(select 1 from tb where [parentid] =t.id)
过滤语句改成你的选择条件,和你这个那个号? delete from @t where id in(select [parentid] from tb)
-->Author:wufeng4552
-->Date :2009-09-30 14:27:56
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[parentid] int,[name] varchar(10))
Insert tb
select 1,0,null union all
select 2,0,null union all
select 3,1,null union all
select 4,3,null union all
select 5,2,null
Go
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(0)t where
id not in(select [parentid] from tb)
/*
ID
-----------
5
4
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-30 14:34:04
-- Version: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[parentid] int,[name] varchar(10))
Insert [tb]
Select 1,0,null union all
Select 2,0,null union all
Select 3,1,null union all
Select 4,3,null union all
Select 5,2,null
Go
--Select * from [tb]-->SQL查询如下:
;with t as
(
select * from tb where parentid=0
union all
select a.* from tb a,t b where a.parentid=b.id
)
select id
from t a
where not exists(
select 1
from t
where a.id=parentid)
/*
id
-----------
5
4(2 行受影响)
*/
as
( select * ,0 as [end] from ta a where parentid = 0
union all
select a.* ,case when exists(select 1 from ta where parentid = a.id) then 0 else 1 end from ta a ,t where a.parentid = t.id)
select * from t
where [end]= 1
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[parentid] int,[name] varchar(10))
Insert tb
select 1,0,null union all
select 2,0,null union all
select 3,1,null union all
select 4,3,null union all
select 5,2,null
Go
-->Title:查找指定節點下的子結點
if object_id('fn_GetChildID')is not null
drop function fn_GetChildID
go
create function fn_GetChildID(@ParentID int)
returns @t table(ID int,PID int)
as
begin
insert @t select ID,[parentid] from tb where ParentID=@ParentID
while @@rowcount>0
begin
insert @t select a.ID,a.[parentid] from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
delete a from @t a where exists(select 1 from @t where a.id=pid)
return
end
go
select ID from dbo.fn_GetChildID(0)
/*
ID
-----------
5
4(2 行受影响)
*/用7L的改一下看是不是你要的.
if not object_id('tb') is null drop table tb
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
Go
CREATE FUNCTION FUN_MU(@ID INT,@FID INT)
RETURNS BIT
AS
BEGIN
DECLARE @TEMPID INT
SELECT @TEMPID=PARENTID FROM TB WHERE ID=@ID
WHILE @TEMPID<>@FID AND @TEMPID IS NOT NULL
BEGIN
SELECT @TEMPID=PARENTID FROM TB WHERE ID=@TEMPID
END
IF @TEMPID=@FID
AND NOT EXISTS(SELECT 1 FROM TB WHERE PARENTID=@ID)
RETURN 1
RETURN 0
END
GO
Create table tb([id] int,[parentid] int,[name] varchar(10))
Insert tb
select 1,0,null union all
select 2,0,null union all
select 3,1,null union all
select 4,3,null union all
select 5,2,null
GoSELECT * FROM TB WHERE DBO.FUN_MU(ID,0)=1/*
4 3 NULL
5 2 NULL
*/
if not object_id('tb') is null drop table tb
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
Go
CREATE FUNCTION FUN_MU(@ID INT,@FID INT)
RETURNS BIT
AS
BEGIN
DECLARE @TEMPID INT
SELECT @TEMPID=PARENTID FROM TB WHERE ID=@ID
WHILE @TEMPID<>@FID AND @TEMPID IS NOT NULL
BEGIN
IF EXISTS
(SELECT PARENTID FROM TB WHERE ID=@TEMPID)
SELECT @TEMPID=PARENTID FROM TB WHERE ID=@TEMPID
ELSE
SELECT @TEMPID=NULL
END
IF @TEMPID=@FID
AND NOT EXISTS(SELECT 1 FROM TB WHERE PARENTID=@ID)
RETURN 1
RETURN 0
END
GO
Create table tb([id] int,[parentid] int,[name] varchar(10))
Insert tb
SELECT 1, 0 ,'体育用品' UNION ALL
SELECT 2, 0 ,'户外运动' UNION ALL
SELECT 3, 1 ,'篮球' UNION ALL
SELECT 4, 1 ,'足球' UNION ALL
SELECT 5, 2 ,'帐篷' UNION ALL
SELECT 6, 2 ,'登山鞋' UNION ALL
SELECT 7, 0 ,'男士用品' UNION ALL
SELECT 8, 7 ,'刮胡刀' UNION ALL
SELECT 9, 3 ,'大号篮球'
GoSELECT * FROM TB WHERE DBO.FUN_MU(ID,1)=1/*
4 1 足球
9 3 大号篮球
*/
我把你的和7楼高手综合了下 删除语句改成7楼的条件
delete from @t where id in(select [parentid] from tb)这个语句功能效率如何?
exists(select 1 from tb where [parentid] =t.id)
过滤语句改成你的选择条件,和你这个那个号?
delete from @t where id in(select [parentid] from tb)
话说LZ看没看偶16L的代码?很简单很方便的