有关于找底层节点

Question

/****** 对象:  Table [dbo].[CLASS]    脚本日期: 02/24/2009 08:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CLASS](
[ID] [int] NOT NULL,
[NAME] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_CLASS_NAME]  DEFAULT (''),
[CLASSCODE] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_CLASS_CLASSCODE]  DEFAULT (''),
[CODE] [nvarchar](500) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_CLASS_CODE]  DEFAULT (''),
[CLASS] [int] NOT NULL CONSTRAINT [DF_CLASS_CLASS]  DEFAULT ((0)),
[SUPER_ID] [int] NOT NULL CONSTRAINT [DF_CLASS_SUPER_ID]  DEFAULT ((0)),
[CREATEDATE] [datetime] NOT NULL CONSTRAINT [DF_CLASS_CREATEDATE]  DEFAULT (getdate()),
[FINISHDATE] [datetime] NOT NULL CONSTRAINT [DF_CLASS_FINISHDATE]  DEFAULT (getdate()),
[ISCLOSE] [int] NOT NULL CONSTRAINT [DF_CLASS_ISCLOSE]  DEFAULT ((0)),
 CONSTRAINT [PK_tbClass] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1表示用途別;
2表示屬性別;
3表示類型別;
4表示具體料件;' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'CLASS', @level2type=N'COLUMN', @level2name=N'CLASS'以上是表结构
ID    NAME     CLASSCODE    CODE    CLASS    SUPER_ID
1     五金      WJ000000     0001     1        0
2     家庭五金  WJ200000     00010001 2        1以上是数据,我传一个ID,得到这个ID的最底层节点ID,请问存储过程怎么写?

Answer 1

这个用的是函数,希望对你用.有一个表名为：T 
结构如下： 
id ---int 
parentid---int 上层的父节点 
sname--varchar(50) 如有以下数据： 
id  parentid  sname 
1  -1         根节点 
2   1         节点1 
3   1         节点2 
4   2         节点3 
5   2         节点4 
6   3         节点5 
7   3         节点6 
7   6         节点7 假如：我要得到某个节点下所有的最底层的节点数据怎么实现： 
如：我要得到id=3 的所有最底层的数据是： 
id  parentid   sname 
7   3          节点6 
7   6          节点7 
请大家多多帮忙，谢谢！ create table tb(id int, parentid int, sname varchar(10))
insert into tb values(1 , -1 ,        '根节点') 
insert into tb values(2 ,  1 ,        '节点1') 
insert into tb values(3 ,  1 ,        '节点2') 
insert into tb values(4 ,  2 ,        '节点3') 
insert into tb values(5 ,  2 ,        '节点4') 
insert into tb values(6 ,  3 ,        '节点5') 
insert into tb values(7 ,  3 ,        '节点6') 
insert into tb values(7 ,  6 ,        '节点7') 
go--查询指定节点及其所有子节点的函数 
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) 
AS 
BEGIN 
  DECLARE @Level int 
  SET @Level=1 
  INSERT @t_Level SELECT @ID,@Level 
  WHILE @@ROWCOUNT>0 
  BEGIN 
    SET @Level=@Level+1 
    INSERT @t_Level SELECT a.ID,@Level 
    FROM tb a,@t_Level b 
    WHERE a.parentid=b.ID 
    AND b.Level=@Level-1 
  END 
  RETURN 
END 
GO SELECT distinct a.* FROM tb a,f_Cid(3) b WHERE a.ID=b.ID and a.id not in(select parentid from tb)drop table tb
drop function dbo.f_cid/*
id          parentid    sname      
----------- ----------- ---------- 
7           3           节点6
7           6           节点7（所影响的行数为 2 行）
*/
--2005
create table tb(id int, parentid int, sname nvarchar(10))
insert into tb values(1 , -1 ,        N'根节点') 
insert into tb values(2 ,  1 ,        N'节点1') 
insert into tb values(3 ,  1 ,        N'节点2') 
insert into tb values(4 ,  2 ,        N'节点3') 
insert into tb values(5 ,  2 ,        N'节点4') 
insert into tb values(6 ,  3 ,        N'节点5') 
insert into tb values(7 ,  3 ,        N'节点6') 
insert into tb values(7 ,  6 ,        N'节点7') 
go;WITH
TREE AS(
    SELECT * FROM TB
    WHERE parentid = 3  -- 要查询的父 id
    UNION ALL
    SELECT TB.* FROM TB, TREE
    WHERE TB.parentid = TREE.id
)
SELECT * FROM TREEdrop table tb

Answer 2

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (ID INT,NAME VARCHAR(8),CLASSCODE VARCHAR(8),CODE VARCHAR(8),CLASS INT,SUPER_ID INT)
INSERT INTO [tb]
SELECT 1,'五金','WJ000000','0001',1,0 UNION ALL
SELECT 2,'家庭五金','WJ200000','00010001',2,1GO
--创建得到子节点的ID的函数
CREATE FUNCTION dbo.fn_GetLastNode(@ID int)
    RETURNS @t TABLE(ID int,SUPER_ID int,level int)
AS
BEGIN
    DECLARE @level int
    
    SET @level=1
    INSERT @t
        SELECT @ID,SUPER_ID,@level
        FROM tb
        WHERE ID=@ID
        
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @level=@level+1
        
        INSERT @t
            SELECT A.ID,A.SUPER_ID,@level
            FROM tb AS A
                JOIN @t AS B
                    ON A.SUPER_ID=B.ID
                        AND B.level=@level-1
    END
    RETURN
ENDGO--SQL查询如下:SELECT * 
FROM dbo.fn_GetLastNode(1) AS A
WHERE NOT EXISTS(
          SELECT *
          FROM dbo.fn_GetLastNode(1)
          WHERE A.ID=SUPER_ID
      )GO
DROP TABLE tb
DROP FUNCTION dbo.fn_GetLastNode

Answer 3

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (ID INT,NAME VARCHAR(8),CLASSCODE VARCHAR(8),CODE VARCHAR(8),CLASS INT,SUPER_ID INT)
INSERT INTO [tb]
SELECT 1,'五金','WJ000000','0001',1,0 UNION ALL
SELECT 2,'家庭五金','WJ200000','00010001',2,1GO
--创建得到子节点的ID的函数
CREATE FUNCTION dbo.fn_GetLastNode(@ID int)
    RETURNS TABLE
AS
    RETURN(
        WITH Liang AS(
            SELECT ID,SUPER_ID,level=1
            FROM tb
            WHERE ID=@ID
            UNION ALL
            SELECT A.ID,A.SUPER_ID,B.level+1
            FROM tb AS A
                JOIN Liang AS B
                    ON B.ID=A.SUPER_ID
        )
        SELECT *
        FROM Liang
    )
GO--SQL查询如下:SELECT * 
FROM dbo.fn_GetLastNode(1) AS A
WHERE NOT EXISTS(
          SELECT *
          FROM dbo.fn_GetLastNode(1)
          WHERE A.ID=SUPER_ID
      )GO
DROP TABLE tb
DROP FUNCTION dbo.fn_GetLastNode

Answer 4

那就尝试用动态SQL了.--动态sql语句基本语法
 
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName 
Exec('select * from tableName') 
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg: 
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句正确 3. 输出参数 
declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select @a=count(*) from tableName ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num 

Answer 5

就是因為在函數里沒辦法執行動態SQL，我在存儲過程里寫了，但是寫的不對，大家幫我改改-- =============================================
-- Author: wkk
-- Create date: 2009/02/16
-- Description: 返回最底層的編號
-- =============================================
ALTER PROCEDURE [dbo].[UP_SelectLastClassID]
@ID INT,
@THISTABLENAME NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(1000)
DECLARE @THISTABLE TABLE(ID INT)
IF(@ID=0)
BEGIN
SET @SQL=N'SELECT ID FROM '+@THISTABLENAME+' WHERE ID NOT IN(SELECT SUPER_ID FROM '+@THISTABLENAME+' WHERE ISCLOSE NOT IN (1)) AND ISCLOSE NOT IN (1)'
END
ELSE
BEGIN
DECLARE @THISCODE NVARCHAR(500)
DECLARE @SUBSQL NVARCHAR(500)
SET @SUBSQL=N'SELECT @THISCODE=CODE FROM '+@THISTABLENAME+' WHERE ISCLOSE NOT IN (1) AND ID='+CAST(@ID AS NVARCHAR(50))
EXEC SP_EXECUTESQL @SUBSQL,N'@THISCODE NVARCHAR(50) OUTPUT',@THISCODE OUTPUT

SET @SQL=N'SELECT ID FROM '+@THISTABLENAME+' WHERE ISCLOSE NOT IN (1) AND ID NOT IN(SELECT SUPER_ID FROM '+@THISTABLENAME+' WHERE ISCLOSE NOT IN (1) AND CODE LIKE ''%'+CAST(@THISCODE AS NVARCHAR(500))+'%'')'
END
EXEC SP_EXECUTESQL @SQL
END

Answer 6

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (ID INT,NAME VARCHAR(8),CLASSCODE VARCHAR(8),CODE VARCHAR(8),CLASS INT,SUPER_ID INT)
INSERT INTO [tb]
SELECT 1,'五金','WJ000000','0001',1,0 UNION ALL
SELECT 2,'家庭五金','WJ200000','00010001',2,1GOCREATE PROC p 
    @ID int,
    @table_name sysname
AS
    DECLARE @SQL nvarchar(4000);
    SET @SQL=N'
        WITH Liang AS
        (
            SELECT
                ID,SUPER_ID,level=1
            FROM ['+@table_name+'] 
            WHERE ID='+RTRIM(@ID)+N'
            UNION ALL
            SELECT B.ID,B.SUPER_ID,A.level+1
            FROM Liang AS A
                JOIN ['+@table_name+N'] AS B
                    ON A.ID=B.SUPER_ID
        )
        SELECT *
        FROM Liang AS A
        WHERE NOT EXISTS(
                 SELECT *
                 FROM Liang
                 WHERE A.ID=SUPER_ID
              )';
    
    EXEC sp_executesql @SQL
GOEXEC p 1,'tb'GO
DROP TABLE tb
DROP PROC p/*
ID          SUPER_ID    level
----------- ----------- -----------
2           1           2(1 行受影响)
*/

Answer 7

直接用CTE递归查询吧，不用函数

Answer 8

借用小梁子的数据
---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (ID INT,NAME VARCHAR(8),CLASSCODE VARCHAR(8),CODE VARCHAR(8),CLASS INT,SUPER_ID INT)
INSERT INTO [tb]
SELECT 1,'五金','WJ000000','0001',1,0 UNION ALL
SELECT 2,'家庭五金','WJ200000','00010001',2,1GOCREATE PROC p 
    @ID int
AS
begin
;with CTE as
(select ID,SUPER_ID, [Level]=1 from tb where ID=@ID
 union all 
 select tb.ID, tb.SUPER_ID, [Level]=CTE.[Level]+1
 from tb join CTE on tb.ID=CTE.SUPER_ID
)
select top 1 ID from CTE order by [Level] desc        
end
GOEXEC p 2
/*
ID
-----------
1(1 行受影响)
*/GO
DROP TABLE tb
DROP PROC p