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 ,'大号篮球' Go DECLARE @FATHER INT SET @FATHER=1 SELECT * FROM TB WHERE DBO.FUN_MU(ID,@FATHER)=1/* 3 1 篮球 4 1 足球 9 3 大号篮球 */
一条语句难实现
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 ,'大号篮球'
Go
DECLARE @FATHER INT
SET @FATHER=1
SELECT * FROM TB WHERE DBO.FUN_MU(ID,@FATHER)=1/*
3 1 篮球
4 1 足球
9 3 大号篮球
*/
http://blog.csdn.net/fredrickhu/archive/2009/09/19/4569529.aspx2005
http://blog.csdn.net/htl258/archive/2009/08/03/4404641.aspx