请教一个数据库表的设计问题

yumanqing 2011-12-01 04:28:25
有一个这样的产品结构：

支架1 由 材料1 + 材料2 组成

支架2 由 材料1 + 材料3 组成

支架3 由 支架1+材料4 组成

这样的一个产品结构，类似BOM，想问问做过的人，表该如何设计，主要是方便统计，可以很方便的统计到最底层的材料清单，谢谢。
...全文
187 19 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
juewangyuai 2011-12-05
  • 打赏
  • 举报
 回复
太复杂啦这的sql！
yumanqing 2011-12-05
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 acherat 的回复:]
SQL code

CREATE TABLE [dbo].[boms] (
picID varchar(20),
piID varchar(20)
)
GO

insert into boms(piID,picID) values('001','0011')
insert into boms(piID,picID) values('001','0012')
in……
[/Quote]

这个可以实现效果，但我现在是一次要查询出来若干个编码的下级材料，还要汇总一下，并不是一次只查询一个材料的，想请问一下如何改进一下呢，谢谢！
AcHerat 元老 2011-12-03
  • 打赏
  • 举报
 回复
这里的递归查询不看表的数据，看递归的层数，就是父级下有多少个子级，2000的话函数应该可以的。
yumanqing 2011-12-03
  • 打赏
  • 举报
 回复
感谢 AcHerat

(小三 [兔子党党务院院长])

热情的回答，效果是可以实现，但SQL 2000用临时表的方式，如果数据量很大，操作频繁的话效率会怎么样呢？
用SQL 2005 支持的 WITH() 效率会不会高很多，若是的，考虑不用SQL 2000了，谢谢
AcHerat 元老 2011-12-03
  • 打赏
  • 举报
 回复 


CREATE TABLE [dbo].[boms] (

	picID varchar(20),

	piID varchar(20)

)

GO



insert into boms(piID,picID) values('001','0011')

insert into boms(piID,picID) values('001','0012')

insert into boms(piID,picID) values('0012','0013')

insert into boms(piID,picID) values('0012','0014')

insert into boms(piID,picID) values('0013','00133')

insert into boms(piID,picID) values('0013','00134')

insert into boms(piID,picID) values('00133','001331')

insert into boms(piID,picID) values('00133','001332')

go



create function f_getP(@piID varchar(20)) 

returns @re table(picID varchar(20),lev int,piID varchar(20),TFlag varchar(10)) 

as

begin

    declare @l int 

    set @l=0 

    insert @re select picID,@l,piID,'本级ID' from [boms] where piID = @piID

    while @@rowcount>0 

    begin 

        set @l=@l+1

        insert @re select a.picID,@l,a.piID,'下级ID'

        from [boms] a,@re b

        where a.piID=b.picID and b.lev=@l-1 and a.picID is not null

    end 

    update @re set lev=@l-lev

    return

end

go



create function f_getC(@piID varchar(20)) 

returns @re table(picID varchar(20),lev int,piID varchar(20),TFlag varchar(10)) 

as

begin

    declare @l int 

    set @l=0 

    insert @re select picID,@l,piID,'本级ID' from [boms] where piID = @piID

    while @@rowcount>0 

    begin 

        set @l=@l+1

        insert @re select a.picID,@l,a.piID,'上级ID'

        from [boms] a,@re b

        where a.picID=b.piID and b.lev=@l-1 and a.piID is not null

    end 

    update @re set lev=@l-lev

    return

end

go



declare @piID varchar(10)

set @piID = '001'



select * from dbo.f_getP(@piID)



select * from dbo.f_getC(@piID)



drop function f_getP,f_getC

drop table [boms]



/*****************************



picID                lev         piID                 TFlag

-------------------- ----------- -------------------- ----------

0011                 4           001                  本级ID

0012                 4           001                  本级ID

0013                 3           0012                 下级ID

0014                 3           0012                 下级ID

00133                2           0013                 下级ID

00134                2           0013                 下级ID

001331               1           00133                下级ID

001332               1           00133                下级ID



(8 行受影响)



picID                lev         piID                 TFlag

-------------------- ----------- -------------------- ----------

0011                 1           001                  本级ID

0012                 1           001                  本级ID



(2 行受影响)
AcHerat 元老 2011-12-03
  • 打赏
  • 举报
 回复
你上一帖不是有写2000的获取所有子级父级的函数么？
yumanqing 2011-12-03
  • 打赏
  • 举报
 回复
你好，我是SQL 2000 ,不是SQL 2005,这样写不行啊
AcHerat 元老 2011-12-03
  • 打赏
  • 举报
 回复 


declare @picID varchar(20)

set @picID = '001'



;with cte as

(

    select picID,piID from tb where picID = @picID

    union all

    select a.picID,a.piID

    from tb a join cte b on a.piID = b.picID

)



select picID

from cte
yumanqing 2011-12-02
  • 打赏
  • 举报
 回复
我现在的物料结构表是这样的

piID --父ID
picID --子ID

有如下面的数据:


insert into boms(piID,picID) values('001','0011')

insert into boms(piID,picID) values('001','0012')

insert into boms(piID,picID) values('0012','0013')

insert into boms(piID,picID) values('0012','0014')

insert into boms(piID,picID) values('0013','00133')

insert into boms(piID,picID) values('0013','00134')

insert into boms(piID,picID) values('00133','001331')

insert into boms(piID,picID) values('00133','001332')


如这样的测试数据，我如何方便的查询到 001 的所有下级目录呢？
谢谢
luomingliang1026 2011-12-01
  • 打赏
  • 举报
 回复
CREATE TABLE [dbo].[CaiLiao] (
[zhijia_hao] [int] NOT NULL PRIMARY KEY ,
[cailiao_hao] [int] not NULL ,
[cailiao_name] [varchar] (50) not NULL,
[cailiao_shu] [varchar] (50) not NULL,

) ON [PRIMARY]
GO

--可能需要材料不止一根，所以加上[cailiao_shu]

CREATE TABLE [dbo].[ZhiJia] (
[zhijia_hao] [int] NOT NULL PRIMARY KEY ,
[zhijia_name] [nvarchar] (50) not NULL ,
[zhijia_shu] [nvarchar] (50) not NULL
) ON [PRIMARY]
GO
--[zhijia_hao]为外键，关联表[dbo].[CaiLiao]。

--运行下面的程序就能分别求出各支架对应需要材料1的数量了。
select b.zhijia_name，zhijia_shu*cailiao_shu
from [dbo].[CaiLiao] a
inner join [dbo].[ZhiJia] b
on a.zhijia_hao=b.zhijia_hao
where a.cailiao_hao=1
group by b.zhijia_hao
luomingliang1026 2011-12-01
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 qianjin036a 的回复:]
产品表(编号,名称,数量)
材料表(编号,产品编号,名称,数量) --此处数量指一个产品所需要的本材料数
[/Quote]

后面用聚合函数就好办了
AcHerat 元老 2011-12-01
  • 打赏
  • 举报
 回复
感觉支架和材料可以放一个表中，当然两个表也可以，这样也行的。
yumanqing 2011-12-01
  • 打赏
  • 举报
 回复 




CREATE TABLE [dbo].[CaiLiao] (

	[iCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,

	[iName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[ZhiJia] (

	[zCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULLPRIMARY KEY ,

	[zName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[ZhiJiaBOM] (

	[zCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

	[iCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 

) ON [PRIMARY]

GO



INSERT INTO CaiLiao(iCode,iName) values('01','材料1')

INSERT INTO CaiLiao(iCode,iName) values('02','材料2')

INSERT INTO CaiLiao(iCode,iName) values('03','材料3')

INSERT INTO CaiLiao(iCode,iName) values('04','材料4')



insert into zhijia(zcode,zname) values('z01','支架1')

insert into zhijia(zcode,zname) values('z02','支架2')

insert into zhijia(zcode,zname) values('z03','支架3')



insert into zhijiabom(zcode,icode) values('z01','01')

insert into zhijiabom(zcode,icode) values('z01','02')



insert into zhijiabom(zcode,icode) values('z02','04')

insert into zhijiabom(zcode,icode) values('z02','03')





insert into zhijiabom(zcode,icode) values('z03','z02')

insert into zhijiabom(zcode,icode) values('z03','03')



类似这样的结构
AcHerat 元老 2011-12-01
  • 打赏
  • 举报
 回复
树形的表结构设计，子级父级那种，查询时采用递归。



--建立 演示环境



if object_id('tb_bookInfo') is not null drop table tb_bookInfo

go

create table tb_bookInfo(number int,name varchar(10),type int)

insert tb_bookInfo

select 1 ,'n1', 6 union all

select 2 ,'n2', 3





if object_id('tb_bookType') is not null drop table tb_bookType

go

create table tb_bookType(id int,typeName varchar(10),parentid int)

insert tb_bookType

select 1,'英语',0 union all

select 2,'生物',0 union all

select 3,'计算机',0 union all

select 4,'口语',1 union all

select 5,'听力',1 union all

select 6,'数据库',3 union all

select 7,'软件工程',3 union all

select 8,'SQL Server',6



select a.*,b.level from tb_bookInfo  a,f_getC(3) b  where a.type=b.id  order by b.level 

/*

number      name       type        level      

----------- ---------- ----------- -----------

2           n2         3           0

1           n1         6           1



（所影响的行数为 2 行）

*/

--查所有父结点

if object_id('f_getP') is not null drop function f_getP

go

create function f_getP(@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.parentid,@l from tb_bookType a,@re b

 where a.id=b.id and b.level=@l-1 and a.parentid<>0

    end 

    update @re set level=@l-level

    return 

end 

go 





--查所有子结点

if object_id('f_getC') is not null drop function f_getC

go

create function f_getC(@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.id,@l from tb_bookType as a,@re as b 

 where b.id=a.parentid and b.level=@l-1

    end

    return 

end 

go 



--查所有父子结点

if object_id('f_getAll') is not null drop function f_getAll

go

create function f_getAll(@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.parentid,@l from tb_bookType a,@re b

 where a.id=b.id and b.level=@l-1 and a.parentid<>0

    end 

    update @re set level=@l-level 

    while @@rowcount>0

    begin 

        set @l=@l+1

        insert @re select a.id,@l from tb_bookType as a,@re as b 

 where b.id=a.parentid and b.level=@l-1

    end

    return

end 

go  

 



--删除演示



drop table tb_bookInfo



drop table tb_bookType



drop function f_getP



drop function f_getC

drop function f_getAll

GO



--sqlserver2005的新方法



-- 建立演示环境

IF OBJECT_ID('[Dept]') IS NOT NULL

    DROP TABLE [Dept]

GO

CREATE TABLE Dept(

 id int PRIMARY KEY,

 parent_id int,

 name nvarchar(20))

INSERT Dept

SELECT 1, 0, N'财务部' UNION ALL

SELECT 2, 0, N'行政部' UNION ALL

SELECT 3, 0, N'业务部' UNION ALL

SELECT 4, 0, N'业务部' UNION ALL

SELECT 5, 4, N'销售部' UNION ALL

SELECT 6, 4, N'MIS' UNION ALL

SELECT 7, 6, N'UI' UNION ALL

SELECT 8, 6, N'软件开发' UNION ALL

SELECT 9, 8, N'内部开发'

GO

--1、父-〉子

-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'MIS'

;WITH

DEPTS AS(

 -- 定位点成员

 SELECT * FROM Dept WHERE name = @Dept_name

 UNION ALL

 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

 SELECT A.*  FROM Dept A, DEPTS B  WHERE A.parent_id = B.id

)

SELECT * FROM DEPTS

GO

--结果如下

/*

id          parent_id   name                

----------- ----------- --------------------

6           4           MIS

7           6           UI

8           6           软件开发

9           8           内部开发



（所影响的行数为 4 行）

*/



--2、子-〉父

-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'内部开发'

;WITH

DEPTS AS(

 -- 定位点成员

  SELECT * FROM Dept WHERE name = @Dept_name

--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent  FROM Dept where @Dept_name

 UNION ALL

 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id

)

SELECT * FROM DEPTS

GO



--结果如下

/*

id          parent_id   name                

----------- ----------- --------------------

9           8           内部开发

8           6           软件开发

6           4           MIS

4           0           业务部



（所影响的行数为 4 行）

*/



-- 删除演示环境

DROP TABLE Dept
--小F-- 2011-12-01
  • 打赏
  • 举报
 回复
物料表(ID....)

物料明细表(ID,PID,....)
yumanqing 2011-12-01
  • 打赏
  • 举报
 回复
我知道有

1.原材料表

2.支架档案表

3.支架构成表

==
支架1 由 材料1 + 材料2 组成

支架2 由 材料1 + 材料3 组成

支架3 由 支架1+材料4 组成

但这种支架结构怎么查询到最底层的原材料呢？
山野市民 2011-12-01
  • 打赏
  • 举报
 回复
表1
id 支架
1 支架1
2 支架2
3 支架4

表二
id 材料
1 材料1
2 材料2
3 材料3
AcHerat 元老 2011-12-01
  • 打赏
  • 举报
 回复
简单的有两个吧！

物料表（基本的物料信息）

物料明细（一些物料的构成信息）
-晴天 2011-12-01
  • 打赏
  • 举报
 回复
产品表(编号,名称,数量)
材料表(编号,产品编号,名称,数量) --此处数量指一个产品所需要的本材料数
一个牛人提供的GIS源码(很好)
这个应该是05年的时候做的毕业设计，用超图的Supermap IS 2003＋SQL Server 2000建立的一个比较的简单的系统，只是玄乎了下就变的有点意思了，甚至也有点学习或者创新的意思。 加上上次发布的论文部分，这样整个系统...
数据库表的设计思路
作为一个程序员你避免不了要设计数据表，总是一味地根据他人给出的表来写sql这样你永远也得不到成长。所以我现在要来思考如何设计出数据库表。        那问题来了，如何设计呢？这...
学生成绩管理系统mysql数据库表,(最新整理)学生成绩管理系统数据库设计
《(最新整理)学生成绩管理系统数据库设计》由会员分享，可在线阅读，更多相关《(最新整理)学生成绩管理系统数据库设计(18页珍藏版)》请在人人文库网上搜索。1、完整)学生成绩管理系统数据库设计(完整)学生成绩管理...
数据库课程设计-图书管理系统数据库设计（缩略版）
历经大概一周的时间，我完成了本次图书管理系统数据库设计实验。从需求分析，到概念模型设计，到逻辑设计，再到最后的的物理设计过程。首先查看本次数据库设计的文件结构图7-1 数据库结构本次实验创建了八个表，除了...
mysql设计数据库表思路_数据库表的设计思路
作为一个程序员你避免不了要设计数据表，总是一味地根据他人给出的表来写sql这样你永远也得不到成长。所以我现在要来思考如何设计出数据库表。那问题来了，如何设计呢？这可是大问题啊...因为我之前就没怎么设计过表...
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章