祖谱数据设计

Question

                                              A                            B
                                             ______________________|______________________
                                            |                           |     
                                    .....  AA1                            BB1  ....                     
                             
                                                                         
                        
              
反正先画一个简单大家都应该祖谱有了解的，请教一下这样子数据要怎么设计！

Answer 1

                             FG001                               | --------------------------------------------------------------------- |                                  |                                |SFG001                           SFG002                           SFG003 |                                   |                                   -------------------               ---------------------                  |                 |               |            |      |WIP001         WIP002           WIP003       WIP004 WIP005       WIP006|                 |               |RAW001 RAW002  RAW003      RAW004,RAW005  ............|                             |KKK001                         KKK003|WWW005 一:先创建BOM表 create table t
(parent varchar(10),
 child varchar(10),qty numeric(9,2)
)insert into t
select 'FG001',  'SFG001',     1  union all
select 'FG001' , 'SFG002',     1 union all
select 'FG001'  ,'SFG003',     1 union all
select 'SFG001', 'WIP001',     2 union all
select 'SFG001' ,'WIP002',     2 union all
select 'SFG002' ,'WIP003',     3 union all
select 'SFG002' ,'WIP004',     3 union all
select 'SFG002' ,'WIP005',     2 union all
select 'SFG003' ,'WIP006',     3 union all
select 'WIP001' ,'RAW001',     2.66 union all
select 'WIP001' ,'RAW002'  ,   2.33 union all
select 'WIP002' ,'RAW003'  ,   3.21 union all
select 'WIP003' ,'RAW004'  ,   1.89 union all
select 'WIP003' ,'RAW005'  ,   1.86 union all
select 'RAW001','KKK001',      3.25 union all
select 'RAW004','KKK003',      4.26 union all
select 'KKK001','WWW005',      5.23
 二:创建函数（a:树型结构显示）  
create function test(@parent VARCHAR(10))
returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2),
                level int,sort Nvarchar(1000)collate Latin1_General_BIN  ) 
as
begin
     declare @level int
     set @level=1
     insert into @t
     select parent,child,qty,@level,parent+child 
     from t
     where parent=@parent collate Latin1_General_BIN 
     while @@rowcount>0
     begin
         set @level=@level+1
         insert @t
         select a.parent,a.child,a.qty*b.qty,@level,b.sort+a.child
         from   t a ,@t b
         where  a.parent=b.child collate Latin1_General_BIN
                and b.level=@level-1   
    end
return
end--调用函数
select
    level,
    space(level*2)+'|--' + child as 'product',
    qty
from 
    dbo.test('FG001')
order by 
    sort 
--结果
/**//*
level       product                         qty         
1             |--SFG001                     1.00
2               |--WIP001                   2.00
3                 |--RAW001                 5.32
4                   |--KKK001               17.29
5                     |--WWW005             90.43
3                 |--RAW002                 4.66
2               |--WIP002                   2.00
3                 |--RAW003                 6.42
1             |--SFG002                     1.00
2               |--WIP003                   3.00
3                 |--RAW004                 5.67
4                   |--KKK003               24.15
3                 |--RAW005                 5.58
2               |--WIP004                   3.00
2               |--WIP005                   2.00
1             |--SFG003                     1.00
2               |--WIP006                   3.00(17 row(s) affected)
*/

Answer 2

谢谢小梁！
数据库表只要parent跟child
那他们关系怎么查询？

Answer 3

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

Answer 4

1张表,  然后加类别判断  加上级编号  就应该能搞定 
这样的问题在程序中实现感觉比用SQL好

Answer 5

参考一下：
---查询某个纪录的所有父类的纪录！     category 表:categoryid     categoryname    parentid
1              新闻            0
2              文化            0
3              国内            1
4              国际            1
5              广东            3
6              广西            3
7              广州            5
当 categoryid 输入 7 时,显示以下结果:1              新闻            0
3              国内            1
5              广东            3
7              广州            5CREATE TABLE category
(
categoryidINT,
categorynameNVARCHAR(20),
parentidINT
)
GOINSERT INTO category SELECT 1,'新闻',0
UNION ALL SELECT 2,'文化',1
UNION ALL SELECT 3,'国内',1
UNION ALL SELECT 4,'国际',3
UNION ALL SELECT 5,'广东',3
UNION ALL SELECT 6,'广西',5
UNION ALL SELECT 7,'广州',5
GO CREATE PROCEDURE proc_catetory @id INT
AS
DECLARE @pid INT
DECLARE @count VARCHAR(50)
SET @count='0'
Label:
SELECT @pid=parentid
FROM category
WHERE categoryid=@id
WHILE EXISTS(SELECT 1 FROM category WHERE categoryid=@id)
BEGIN
        SET @count=@count+CAST(@id AS VARCHAR(5))
        SET @id=@pid  
        GOTO Label
ENDSET @count= CAST(CAST(@count AS INT) AS VARCHAR(50))
SELECT * 
FROM category
WHERE CHARINDEX(CAST(categoryid AS VARCHAR(5)),@count)<>0 EXEC proc_catetory 7
categoryid  categoryname         parentid    
----------- -------------------- ----------- 
1           新闻                   0
3           国内                   1
5           广东                   3
7           广州                   5当 categoryid 输入 3 时,显示以下结果:1              新闻            0
3              国内            1
当 categoryid 输入 1 时,显示以下结果:1              新闻            0 

Answer 6

---查询指定根目录下所有节点！     ----创建测试数据
if object_id('tbType') is not null
drop table tbType
if object_id('tbTestProduct') is not null
drop table tbTestProduct
if object_id('fnGetChildren') is not null
drop function fnGetChildren
GO
create table tbType(类别编号 int,类别名称 varchar(20),父类编号 int)
insert tbType
select 1,          '食品',       NULL union all
select 2,          '副食品',     1 union all
select 3,          '糖',         2 union all
select 4,          '白糖',       3 union all
select 5,          '红糖',       3 union all
select 6,          '绵白糖',     4 union all
select 7,       '梅花牌绵白糖',  6 union all
select 8,          '饮料',       2 union all
select 9,         '碳酸饮料',    8 union all
select 10,       '维生素饮料',   8 union all
select 11,         '雪碧',       9 union all
select 12,         '可乐',       9 union all
select 13,         '粒粒橙',     10 union all
select 14,         '主食品',     1 union all
select 15,         '肉类',       14 union all
select 16,         '蔬菜',       14
create table tbTestProduct(商品编号 int,商品名称 varchar(20),类别编号 int)
GO
----创建子结点查找函数
create function fnGetChildren(@id int)
returns @tmp table(类别编号 int,类别名称 varchar(20))
as
begin
    insert @tmp select 类别编号,类别名称 from tbType where 类别编号 = @id
    while @@rowcount > 0
    insert @tmp select a.类别编号,a.类别名称 from tbType as a INNER JOIN @tmp as b
    on a.父类编号 = b.类别编号 where a.类别编号 not in(select 类别编号 from @tmp)
return
end
GO----查询所有子类
declare @id int
set @id = 2
select * from dbo.fnGetChildren(@id)
----查询子类的所有商品
select * from dbo.fnGetChildren(@id) as a
left join tbTestProduct as b on a.类别编号 = b.类别编号----清除测试环境
drop table tbType,tbTestProduct
drop function fnGetChildren/*结果
类别编号        类别名称                 
----------- -------------------- 
2           副食品
3           糖
8           饮料
4           白糖
5           红糖
9           碳酸饮料
10          维生素饮料
6           绵白糖
11          雪碧
12          可乐
13          粒粒橙
7           梅花牌绵白糖
（所影响的行数为 12 行）
*/ 

Answer 7

qty numeric(9,2）是什么意思？

Answer 8

qty 列名numeric(9,2） 列数据类型

Answer 9

就是个树型表即可.-- 树形数据层次显示处理示例
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/--树形数据广度排序处理示例.
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--广度排序显示处理
--生成每个节点的层次数据
DECLARE @t_Level TABLE(ID char(3),Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID   PID  Name       
------- --------- ---------- 
001  NULL 山东省
005  NULL 四会市
002  001   烟台市
003  001   青岛市
006  005   清远市
004  002   招远市
007  006   小分市
--*/
-- 树形数据深度排序处理示例(递归法)
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT  --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID   PID   Name       
------ --------- ---------- 
001  NULL 山东省
002  001   烟台市
004  002   招远市
003  001   青岛市
005  NULL 四会市
006  005   清远市
007  006   小分市
--*/

Answer 10

一个表就可以了。ID ParentID ……