求一条SQL语句，查询所有父级分类的数据（包括自己）

Question

原表数据，如图
已知CID=285，求查询结果如图（得到的所有父级分类数据及本身）

Answer 1

/*
标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 10 行）
*/--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
002  001  广州市
004  002  天河区（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 7 行）
*/drop table tb
drop function f_cid@@ROWCOUNT：返回受上一语句影响的行数。
返回类型：integer。
注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
   print 'Warning: No rows were updated'结果：(所影响的行数为 0 行)
Warning: No rows were updated
/*
标题：SQL SERVER 2005中查询指定节点及其所有子节点的方法(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：新疆乌鲁木齐
*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
goDECLARE @ID VARCHAR(3)--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇(10 行受影响)
*/--查询ID = '002'的所有子节点
SET @ID = '002'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
002  001  广州市
004  002  天河区(2 行受影响)
*/--查询ID = '003'的所有子节点
SET @ID = '003'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇(7 行受影响)
*/drop table tb--注：除ID值不一样外，三个SQL语句是一样的。

Answer 2

参考，非常相似CREATE TABLE [dbo].[temptb](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [pid] [int] NULL,
    [name1] [varchar](20) ,
    [name] [nvarchar](50) ,
    [parentid] [int] NULL,
 CONSTRAINT [PK_temptb] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]GO/**//* 创建函数  根据节点id找出其所有父节点*/
create   function   f_pid(@id   int)  
  returns   @re   table(id   int,level   int)  
  as  
  begin  
  declare   @l   int  
  set   @l=0  
  insert   @re   select   @id,@l  
  while   @@rowcount>0  
  begin  
  set   @l=@l+1  
  insert   @re   select   a.pid,@l  
  from   temptb   a,@re   b  
  where   a.id=b.id    
  and   b.level=@l-1  
  and   a.pid<>0  
  end  
  update   @re   set   level=@l-level  
  return  
  end  
  go  
  
   /**//* */
  select   a.*,b.level  
  from   temptb   a,f_pid(7)   b  
  where   a.id=b.id  
  order   by   b.level  
  go    

Answer 3

查询指定节点及其所有子节点或父节点
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb(id VARCHAR(3),pid VARCHAR(3),[name] VARCHAR(10))
GO
INSERT INTO tb SELECT '001',NULL,'河南省'
UNION ALL SELECT '002','001','洛阳市'
UNION ALL SELECT '003','001','新乡市'
UNION ALL SELECT '004','002','栾川县'
UNION ALL SELECT '005','003','长垣县'
UNION ALL SELECT '006','002','孟津县'
UNION ALL SELECT '007','004','冷水乡'
UNION ALL SELECT '008','004','叫河乡'
UNION ALL SELECT '009','008','A村'
UNION ALL SELECT '010','008','B村'
GO
/*
1、查询指定节点及其所有子节点
--如：已知 栾川县
--得到结果id   pid  name
---- ---- ----------
004  002  栾川县
007  004  冷水乡
008  004  叫河乡
009  008  A村
010  008  B村
*/****************************查询开始******************************
--SQL2005 使用CTE
DECLARE @ VARCHAR(10)
SET @='栾川县'
;WITH XiaoAi AS
(
SELECT id FROM tb WHERE [name]=@
UNION ALL
SELECT t.id FROM XiaoAi AS a INNER JOIN tb AS t ON a.id=t.pid
)
SELECT t.* FROM XiaoAi--SQL 2000 使用函数
IF OBJECT_ID('dbo.XiaoAi') IS NOT NULL DROP FUNCTION dbo.XiaoAi
GO
CREATE FUNCTION dbo.XiaoAi(@ VARCHAR(20))
RETURNS @t TABLE(id VARCHAR(3), pid VARCHAR(3), [name] VARCHAR(20),Level INT)
AS
BEGIN
    DECLARE @level INT
    SET @level=1
    INSERT INTO @t SELECT *,@level FROM tb WHERE [name]=@
    WHILE(@@ROWCOUNT>0)
        BEGIN
            SET @level=@level+1
            INSERT INTO @t SELECT t.*,@level FROM tb AS t,@t AS a WHERE a.id=t.pid AND a.level=@level-1
        END
RETURN
END
GO
--调用函数
SELECT id, pid,[name]  from dbo.XiaoAi('栾川县')
--**************************** 查询结束*************************************
/*
2、查询指定节点及其所有父节点
如：已知 栾川县
得到以下结果
id   pid  name
---- ---- ----------
001  NULL 河南省
002  001  洛阳市
004  002  栾川县
*/
----------------------测试开始 -------------------------------------------------
DECLARE @s VARCHAR(10)
SET @s='栾川县'
SELECT id, pid,[name] INTO # FROM tb WHERE [name]=@s
WHILE @@ROWCOUNT>0
    BEGIN
    INSERT INTO # SELECT t.id, t.pid,t.[name] FROM tb AS t
                                INNER JOIN # AS a ON t.id=a.pid AND t.id NOT IN(SELECT ID FROM #)
    END
SELECT * FROM # ORDER BY ID

Answer 4

--sql 2000
--查询指定节点及其所有子节点的函数
create function f_cid(@cID int) returns @t_level table(cid int , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @cid , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.cid , @level
    from tb a , @t_Level b
    where a.cuplevel = b.cid and b.level = @level - 1
  end
  return
end
goselect a.* from tb a , f_cid(285) b where a.cid = b.cid order by a.cid--sql 2005DECLARE @cID int
SET @cID = 285
;WITH T AS
(
  SELECT cID , cuplevel , NAME 
  FROM TB
  WHERE cID = @cID
  UNION ALL
  SELECT A.cID , A.cuplevel , A.NAME 
  FROM TB AS A JOIN T AS B ON A.cuplevel = B.cID
)
SELECT * FROM T ORDER BY cID

Answer 5

;with f as
(
select * from tb where CID=285
union all
select a.* from tb a,f where a.cuplevel=a.cid
)
select * from f

Answer 6

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([CID] [int],[Cname] [nvarchar](10),[Clevel] [int],[CUpLevel] [int])
INSERT INTO [tb]
SELECT '265','珠宝首饰','1','0' UNION ALL
SELECT '266','户外运动','1','0' UNION ALL
SELECT '267','数码视听','1','0' UNION ALL
SELECT '268','生活日用','1','0' UNION ALL
SELECT '278','精品鞋包','1','0' UNION ALL
SELECT '279','美容护肤','1','0' UNION ALL
SELECT '280','流行饰品','2','265' UNION ALL
SELECT '281','珠宝首饰','2','265' UNION ALL
SELECT '282','品牌手表','2','265' UNION ALL
SELECT '283','品牌眼镜','2','265' UNION ALL
SELECT '284','男人饰品','2','265' UNION ALL
SELECT '285','潮流女鞋','2','278' UNION ALL
SELECT '286','品质男鞋','2','278' UNION ALL
SELECT '287','潮流箱包','2','278' UNION ALL
SELECT '288','商务箱包','2','278' UNION ALL
SELECT '289','高跟鞋','3','285' UNION ALL
SELECT '290','单鞋','3','285' UNION ALL
SELECT '291','皮鞋','3','285' UNION ALL
SELECT '292','凉鞋','3','285' UNION ALL
SELECT '293','凉拖','3','285' UNION ALL
SELECT '294','帆布鞋','3','285' UNION ALL
SELECT '296','休闲鞋','3','285' UNION ALL
SELECT '297','增高鞋','3','285' UNION ALL
SELECT '298','其它女鞋','3','285'--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GOCREATE FUNCTION [fn_test]
(
@CID INT
)
RETURNS @t TABLE(cid INT, CUpLevel INT, lvl INT)
AS
BEGIN
DECLARE @lvl INT, @lvl1  INT
DECLARE @t1 TABLE(cid INT, CUpLevel INT, lvl INT)
SET @lvl = 0
INSERT @t
SELECT CID, CUpLevel, @lvl 
FROM tb
WHERE cid = @CID
WHILE @@ROWCOUNT>0
BEGIN
    SET @lvl = @lvl+1
    INSERT @t
    SELECT a.cid, a.CUpLevel, @lvl 
    FROM tb a, @t b
    WHERE a.CUpLevel = b.cid
     AND b.lvl = @lvl- 1
END
SET @lvl1 = 0
INSERT @t1
SELECT CID, CUpLevel, @lvl1 
FROM tb
WHERE cid = @CID
WHILE @@ROWCOUNT>0
BEGIN
    SET @lvl1 = @lvl1+1
    INSERT @t1
    SELECT a.cid, a.CUpLevel, @lvl1 
    FROM tb a, @t1 b
    WHERE b.CUpLevel = a.cid
     AND b.lvl = @lvl1- 1
END
INSERT @t
SELECT * 
FROM @t1 a
WHERE NOT EXISTS(
          SELECT 1 
          FROM @t
          WHERE cid = a.cid
      )
RETURN
END
GOSELECT a.* 
FROM tb a
JOIN [fn_test](285) b
ON  a.cid = b.cid
ORDER BY a.cid/*
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278
289         高跟鞋        3           285
290         单鞋         3           285
291         皮鞋         3           285
292         凉鞋         3           285
293         凉拖         3           285
294         帆布鞋        3           285
296         休闲鞋        3           285
297         增高鞋        3           285
298         其它女鞋       3           285(11 行受影响)
*/

Answer 7

不好意思，是我误导大家了，道歉，为表赚意，再加50分
其实我的需求是这样的（SQL2000）：
如果已知CID=289，我根据CID得到CLevel＝3，查询上级分类及再上一级分类，那么我需要得到这三条数据，
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278
289         高跟鞋        3           285
如果已知CID=285,我根据CID得到它的clevel=2，需要查询上一级，那么我需要得到这二条数据，
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278
如果已知CID=278,我根据CID得到它的clevel=1，那么我只需要得到这一条数据，
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
或许可以这样判断CUpLevel是否等于0，不等于就查询，直到查询到CUpLevel=0
其实我这个表总共就三个级别，打个比方（比如：孙子，爸爸，爷爷）
如果已知的是孙子的ID，则要查询孙子爸爸和爷爷的数据
如果已知的是爸爸的ID，则查询爸爸和爷爷的数据
如果已知的是爷爷的ID，那么只需查询爷爷的数据这样应该不用那么复杂的SQL吧，

Answer 8

这样的查询应该算是比较能的了，如果不能用CTE的话的

Answer 9

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([CID] [int],[Cname] [nvarchar](10),[Clevel] [int],[CUpLevel] [int])
INSERT INTO [tb]
SELECT '265','珠宝首饰','1','0' UNION ALL
SELECT '266','户外运动','1','0' UNION ALL
SELECT '267','数码视听','1','0' UNION ALL
SELECT '268','生活日用','1','0' UNION ALL
SELECT '278','精品鞋包','1','0' UNION ALL
SELECT '279','美容护肤','1','0' UNION ALL
SELECT '280','流行饰品','2','265' UNION ALL
SELECT '281','珠宝首饰','2','265' UNION ALL
SELECT '282','品牌手表','2','265' UNION ALL
SELECT '283','品牌眼镜','2','265' UNION ALL
SELECT '284','男人饰品','2','265' UNION ALL
SELECT '285','潮流女鞋','2','278' UNION ALL
SELECT '286','品质男鞋','2','278' UNION ALL
SELECT '287','潮流箱包','2','278' UNION ALL
SELECT '288','商务箱包','2','278' UNION ALL
SELECT '289','高跟鞋','3','285' UNION ALL
SELECT '290','单鞋','3','285' UNION ALL
SELECT '291','皮鞋','3','285' UNION ALL
SELECT '292','凉鞋','3','285' UNION ALL
SELECT '293','凉拖','3','285' UNION ALL
SELECT '294','帆布鞋','3','285' UNION ALL
SELECT '296','休闲鞋','3','285' UNION ALL
SELECT '297','增高鞋','3','285' UNION ALL
SELECT '298','其它女鞋','3','285'--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GOCREATE FUNCTION [fn_test]
(
    @CID INT
)
RETURNS @t TABLE(cid INT, CUpLevel INT, lvl INT)
AS
BEGIN
    DECLARE @lvl INT
    SET @lvl = 0
    INSERT @t
    SELECT CID, CUpLevel, @lvl 
    FROM tb
    WHERE cid = @CID
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @lvl = @lvl+1
        INSERT @t
        SELECT a.cid, a.CUpLevel, @lvl 
        FROM tb a, @t b
        WHERE b.CUpLevel = a.cid
            AND b.lvl = @lvl- 1
    END
    RETURN
END
GOSELECT a.* 
FROM tb a
    JOIN [fn_test](285) b
        ON  a.cid = b.cid
ORDER BY a.cid
/*
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278(2 行受影响)
*/SELECT a.* 
FROM tb a
    JOIN [fn_test](289) b
        ON  a.cid = b.cid
ORDER BY a.cid
/*
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278
289         高跟鞋        3           285(3 行受影响)
*/

Answer 10

谢谢各位了，特别是TONY（15楼），再想问一下，如果只需查找父级，不要本身怎么修改也就是根据CID=289,得到两条数据CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278

Answer 11

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([CID] [int],[Cname] [nvarchar](10),[Clevel] [int],[CUpLevel] [int])
INSERT INTO [tb]
SELECT '265','珠宝首饰','1','0' UNION ALL
SELECT '266','户外运动','1','0' UNION ALL
SELECT '267','数码视听','1','0' UNION ALL
SELECT '268','生活日用','1','0' UNION ALL
SELECT '278','精品鞋包','1','0' UNION ALL
SELECT '279','美容护肤','1','0' UNION ALL
SELECT '280','流行饰品','2','265' UNION ALL
SELECT '281','珠宝首饰','2','265' UNION ALL
SELECT '282','品牌手表','2','265' UNION ALL
SELECT '283','品牌眼镜','2','265' UNION ALL
SELECT '284','男人饰品','2','265' UNION ALL
SELECT '285','潮流女鞋','2','278' UNION ALL
SELECT '286','品质男鞋','2','278' UNION ALL
SELECT '287','潮流箱包','2','278' UNION ALL
SELECT '288','商务箱包','2','278' UNION ALL
SELECT '289','高跟鞋','3','285' UNION ALL
SELECT '290','单鞋','3','285' UNION ALL
SELECT '291','皮鞋','3','285' UNION ALL
SELECT '292','凉鞋','3','285' UNION ALL
SELECT '293','凉拖','3','285' UNION ALL
SELECT '294','帆布鞋','3','285' UNION ALL
SELECT '296','休闲鞋','3','285' UNION ALL
SELECT '297','增高鞋','3','285' UNION ALL
SELECT '298','其它女鞋','3','285'--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GOCREATE FUNCTION [fn_test]
(
    @CID INT
)
RETURNS @t TABLE(cid INT, CUpLevel INT, lvl INT)
AS
BEGIN
    DECLARE @lvl INT
    SET @lvl = 0
    INSERT @t
    SELECT CID, CUpLevel, @lvl 
    FROM tb
    WHERE cid = @CID
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @lvl = @lvl+1
        INSERT @t
        SELECT a.cid, a.CUpLevel, @lvl 
        FROM tb a, @t b
        WHERE b.CUpLevel = a.cid
            AND b.lvl = @lvl- 1
    END
    DELETE @t WHERE cid=@cid
    RETURN
END
GOSELECT a.* 
FROM tb a
    JOIN [fn_test](289) b
        ON  a.cid = b.cid
ORDER BY a.cid
/*
CID         Cname      Clevel      CUpLevel
----------- ---------- ----------- -----------
278         精品鞋包       1           0
285         潮流女鞋       2           278(2 行受影响)
*/