族谱中已知一辈份id求求其族谱树直系上辈及小辈

Question

在网上无意间看到一文，可惜没有看到答案，贴过来大家一块看看
表内容为：+---------------------------+
| ID | PARENT |    NAME     |
+----+--------+-------------+
| 1  | 0      |    祖父      |
+----+--------+-------------+
| 2  | 1      |    父亲      |
+----+--------+-------------+
| 3  | 1      |    叔伯      |
+----+--------+-------------+
| 4  | 2      |    自己      |
+----+--------+-------------+
| 5  | 4      |    儿子      |
+----+--------+-------------+
| 6  | 5      |    孙子      |
+----+--------+-------------+
| 7  | 2      |    姐妹      |
+----+--------+-------------+
| 8  | 3      |    表亲      |
+----+--------+-------------+
| 9  | 7      |    甥儿      |
+----+--------+-------------+
| 10 | 4      |    女儿      |
+----+--------+-------------+
| 11 | 10     |    外孙      |
+----+--------+-------------+
| 12 | 5      |    孙女      |
+----+--------+-------------+
| .. | ...    |    ....     |
+---------------------------+
以上为族系表family。求以下问题的SQL命令。
请注意：
请以一句SQL语句来实现，
famliy表是无限族系表。直系上辈还有曾祖、曾曾祖……小辈还有曾孙、曾曾孙……等可能。1、已知任一ID，求上辈族系树（从幼到长顺序），
如已知 ID=11, 返回 :11, 10, 外孙
10, 4, 女儿
4, 2, 自己
2, 1, 父亲
1, 0, 祖父2、已知任一ID，求其小辈列表，
如已知 ID=4，返回 :4, 2, 自己
5, 4, 儿子
6, 5, 孙子
12, 5, 孙女
10, 4, 女儿
11, 10, 外孙不限MYSQL了，MSSQL也可。
:)

Answer 1

其实就是个无限分类的问题,没有想出来用一句语句实现的方法,实现的方法有很多种,我来发一个简单的方法抛砖引玉:
1、已知任一ID，求上辈族系树（从幼到长顺序）:
CREATE PROC GetParentsStr --获取所有上级目录id和name
@id int
AS 
begin
  declare @re_idStr varchar(1000)--没有无限,按18代算都足够了
  set @re_idStr = ''
  select @re_idStr = convert(varchar(8),id) from yourtable where id = @id
  while exists (select 1 from yourtable where id = @id and Parent > 0)
    begin
      select @id = b.id , @re_idStr =@re_idStr+','+convert(varchar(8),b.id) from yourtable a , yourtable b where a.id = @id and a.Parent = b.id
    end
  select id,parent,name from yourtable where CHARINDEX(',' + RTRIM(id) + ',' , ',' + RTRIM(@re_idStr) + ',')>0 order by id desc
end
GO

Answer 2

2、已知任一ID，求其小辈列表:CREATE PROC GetChildrenStr --获取当前辈分所有子辈
@id int
AS 
begin
  declare @@re_str varchar(4000)
  set @@re_str=''
  select id,parent,name from yourtable where CHARINDEX(',' + RTRIM(id) + ',' , ',' + RTRIM(@id+','+getParentidStr(@id)) + ',')>0 order by id asc
end
create function getParentidStr(@ids varchar(1000))
as
begin
  declare @re_idstr varchar(1000)
  set @re_idstr=''
  while (ids<>'')
    begin
      select @re_idstr =@re_idstr +','+convert(varchar(8),id) from yourtable where parent in (ids)
    end
  set @@re_str=@@re_str+','+@re_idstr
  if(@re_idstr<>'')
  begin
    getParentidStr(@re_idstr)
  end
  return @@re_str
end
GO由于时间关系,没有验证

Answer 3

参考：http://blog.csdn.net/dobear_0922/archive/2008/04/18/2304165.aspx

Answer 4

我以前用MySQL写的.mysql> select * from tb_test;
+------------+--------------+--------------+
| ProdCateID | ProdCateName | ParentCateID |
+------------+--------------+--------------+
|          1 | 服装            |            0 |
|          2 | 箱包             |            0 |
|          3 | 内衣             |            1 |
|          4 | 外套             |            1 |
|          5 | 男箱包             |            2 |
|          6 | 女箱包            |            2 |
|          7 | 内裤            |            3 |
|          8 | 文胸             |            3 |
|          9 | 男外套             |            4 |
|         10 | 女大衣            |            4 |
|         11 | 男用钱包            |            5 |
|         12 | 女用钱包           |            6 |
+------------+--------------+--------------+
SP代码如下:
DELIMITER $$DROP PROCEDURE IF EXISTS `tennis`.`sp_tree_test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_tree_test`(in parent_id int)
begin
    declare level smallint default 0;
    declare cnt int default 0;
    create temporary table tt(ProdCateID int,ProdCateName varchar(20),
                              ParentCateID int,level smallint,sort varchar(1000));
    create temporary table tt2(ProdCateID int,ProdCateName varchar(20),
                              ParentCateID int,level smallint,sort varchar(1000));    insert into tt select ProdCateID,ProdCateName,
                          ParentCateID,level,cast(ProdCateID as char)
                   from tb_test
                   where ParentCateID=parent_id;    select row_count() into cnt;
    insert into tt2 select * from tt;    while cnt>0 do
        set level=level+1;
        truncate table tt;
        insert into tt select a.ProdCateID,a.ProdCateName,
                              a.ParentCateID,level,concat(b.sort,a.ProdCateID)
                       from tb_test a,tt2 b
                       where a.ParentCateID=b.ProdCateID and b.level=level-1;
        select row_count() into cnt;
        insert into tt2 select * from tt;
    end while;
    select ProdCateID,
           concat(space(a.level*2),'|--',a.ProdCateName) ProdCateName
    from tt2 a
    order by sort;    drop table tt;
    drop table tt2;
end $$DELIMITER ;##执行mysql> call sp_tree_test(0);
+------------+-----------------+
| ProdCateID | ProdCateName    |
+------------+-----------------+
|          1 | |--服装            |
|          3 |   |--内衣           |
|          7 |     |--内裤        |
|          8 |     |--文胸         |
|          4 |   |--外套           |
|         10 |     |--女大衣        |
|          9 |     |--男外套         |
|          2 | |--箱包             |
|          5 |   |--男箱包           |
|         11 |     |--男用钱包        |
|          6 |   |--女箱包          |
|         12 |     |--女用钱包       |
+------------+-----------------+
12 rows in set (0.30 sec)

Answer 5

这个帖子哪里都在讨论噢,真够火的,就是不知道能不能找出那个能一句sql写完的人,哈哈,我也很想知道啊

Answer 6

小梁子还会MySQL呀，强悍！

Answer 7

呵呵.不强..我学SQL Server之前是学MySQL的..

Answer 8

create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'祖父
insert into os select 2,1,'父亲'
insert into os select 3,1,'伯父'
insert into os select 4,2,'我'
insert into os select 5,2,'哥哥'
insert into os select 6,2,'姐姐'
insert into os select 7,1,'叔叔'
insert into os select 7,,'阿姨'
--求个节点下所有子节点：
create function f_cid(@id int)
returns varchar(500)
as
begin
     declare @t table(id int,parentid int,desn varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  os where id=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from os a,@t b
          where a.parentid=b.id and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
     return @cids
end
go--调用函数
select *,ids=dbo.f_cid(id) from os
--得到每个节点路径：
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath  into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id 
while @j<=@i
begin
       update #os set fullpath=a.fullpath+','+ltrim(#os.id) 
            from #os inner join #os a on #os.parentid=a.id 
       where #os.parentid=@j 
       set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1
这是朋友写的方法,去试下,注意要灵活应用,这是从BOM变化而来

Answer 9

--CREATE TABLE Family (ID INT,Parent INT, Name NVARCHAR(10))
1、已知任一ID，求上辈族系树（从幼到长顺序），如已知 ID=11, 返回 : 
11, 10, 外孙 
10, 4, 女儿 
4, 2, 自己 
2, 1, 父亲 
1, 0, 祖父 
WITH Familys (ID,Parent,Name) AS 
(
SELECT ID,Parent,Name FROM Family WHERE ID=4
UNION ALL 
SELECT Family.ID,Family.Parent,Family.Name FROM Familys INNER JOIN Family
ON Familys.Parent=Family.ID
)
SELECT  * FROM Familys2、已知任一ID，求其小辈列表， 如已知 ID=4，返回 : 
4, 2, 自己 
5, 4, 儿子 
6, 5, 孙子 
12, 5, 孙女 
10, 4, 女儿 
11, 10, 外孙 
WITH Familys (ID,Parent,Name) AS 
(
SELECT ID,Parent,Name FROM Family WHERE ID=4
UNION ALL 
SELECT Family.ID,Family.Parent,Family.Name FROM Familys INNER JOIN Family
ON Familys.ID=Family.Parent
)
SELECT  * FROM Familys

Answer 10

14楼的跟以前一个朋友回复我的帖子似乎只有参数不一样啊真是无敌
可以在csdn搜一下旧帖子。