询问一个SQL语句，或者视图或者存储过程

Question

表结构：
ID int --主键自增长
U_Name varchar(20)  --用户名
P_ID int   --上级用户ID，默认0
我想查出如下的列表：ID    用户名    该用户创建的用户数量（就是P_id=这个用户的ID的数量）
感谢

Answer 1

/*
标题：查询指定节点及其所有子节点的函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2009-10-20
地点：陕西西安
地址：http://topic.csdn.net/u/20091020/18/a667c3dd-07e4-414d-9200-06c35d3d31a9.html
*/
ID parentid(下级级ID)  名称  数量 
1  0                名称1  1 
2  1                名称2  1 
3  1                名称3  1 
4  2                名称4  1 
5  2                名称5  1 
6  3                名称6  1 
7  0                名称7  1 
8  7                名称8  1 
9  7                名称9  1 合计    6（名称1 以下所有节点 合计数量） 
名称1  1 
合计    3(名称2以下有节点) 
名称2  1 
名称4  1 
名称5  1 
合计    2 
名称3  1 
名称6  1 create table tb(ID int,parentid int, name varchar(10) ,cnt int)
insert into tb values(1 , 0 , '名称1' , 1) 
insert into tb values(2 , 1 , '名称2' , 1) 
insert into tb values(3 , 1 , '名称3' , 1) 
insert into tb values(4 , 2 , '名称4' , 1) 
insert into tb values(5 , 2 , '名称5' , 1) 
insert into tb values(6 , 3 , '名称6' , 1) 
insert into tb values(7 , 0 , '名称7' , 1) 
insert into tb values(8 , 7 , '名称8' , 1) 
insert into tb values(9 , 7 , '名称9' , 1)
go
--创建临时表
create table tmp (name varchar(10) ,cnt int)
go
--创建查询指定节点及其所有子节点的函数
create function f_cid(@ID int) returns @t_level table(id int , 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.parentid = b.id and b.level = @level - 1
  end
  return
end
go--创建存储过程并将数据插入临时表
create proc my_proc 
as
begin
  declare @id as int
  declare @cnt as int
  declare @name as varchar(10)
  set @id = 0
  while exists(select 1 from tb where id > @id)
  begin
    set @id = (select min(id) from tb where id > @id)
    set @name = (select name from tb where id = @id)
    set @cnt = (select sum(cnt) from (select a.* from tb a , f_cid(@id) b where a.id = b.id ) t)
    insert into tmp select @name , @cnt
  end
end
go
exec my_procselect * from tmpdrop table tb , tmp
drop function f_cid
drop proc my_proc/*
name       cnt         
---------- ----------- 
名称1        6
名称2        3
名称3        2
名称4        1
名称5        1
名称6        1
名称7        3
名称8        1
名称9        1（所影响的行数为 9 行）*/

Answer 2

2000如一楼那样做.
2005如下:/*
标题：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 3

CREATE TABLE tb_user(id INT IDENTITY(1,1) PRIMARY KEY, username VARCHAR(50), parentid INT)
GoINSERT tb_user SELECT '用户1',0 UNION ALL
SELECT '用户2',1 UNION ALL
SELECT '用户3',2 UNION ALL
SELECT '用户4',3 UNION ALL
SELECT '用户5',1
Go
-->SELECT * FROM tb_user
/**************************************************************************
id          username                                           parentid
----------- -------------------------------------------------- -----------
1           用户1                                                0
2           用户2                                                1
3           用户3                                                2
4           用户4                                                3
5           用户5                                                1
**************************************************************************/
WITH T(id,username,sp) AS
(SELECT id,username, 1 AS sp FROM tb_user WHERE parentid=0
UNION ALL 
SELECT tb_user.id,tb_user.username,T.sp+1 AS sp FROM tb_user, T WHERE tb_user.parentid=T.id)
SELECT id,username,(SELECT COUNT(1) FROM tb_user WHERE tb_user.parentid=T.id) AS createnum FROM T
/***************************************************************************
id          username                                           createnum
----------- -------------------------------------------------- -----------
1           用户1                                                2
2           用户2                                                1
5           用户5                                                0
3           用户3                                                1
4           用户4                                                0
***************************************************************************/