表1 id 支架 1 支架1 2 支架2 3 支架4表二 id 材料 1 材料1 2 材料2 3 材料3
物料表(ID....)物料明细表(ID,PID,....)
树形的表结构设计,子级父级那种,查询时采用递归。 --建立 演示环境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',6select 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_bookInfodrop table tb_bookTypedrop function f_getPdrop 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
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] GOCREATE 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] GOCREATE TABLE [dbo].[ZhiJiaBOM] ( [zCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [iCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOINSERT 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')类似这样的结构
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
我现在的物料结构表是这样的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 的所有下级目录呢? 谢谢
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
你好,我是SQL 2000 ,不是SQL 2005,这样写不行啊
CREATE TABLE [dbo].[boms] ( picID varchar(20), piID varchar(20) ) GOinsert 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') gocreate 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 gocreate 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 godeclare @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 行受影响)
id 支架
1 支架1
2 支架2
3 支架4表二
id 材料
1 材料1
2 材料2
3 材料3
--建立 演示环境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',6select 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_bookInfodrop table tb_bookTypedrop function f_getPdrop 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
[iCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
[iName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE 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]
GOCREATE TABLE [dbo].[ZhiJiaBOM] (
[zCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[iCode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOINSERT 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')类似这样的结构
[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
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 的所有下级目录呢?
谢谢
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
CREATE TABLE [dbo].[boms] (
picID varchar(20),
piID varchar(20)
)
GOinsert 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')
gocreate 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
gocreate 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
godeclare @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 行受影响)
(小三 [兔子党党务院院长])热情的回答,效果是可以实现,但SQL 2000用临时表的方式,如果数据量很大,操作频繁的话效率会怎么样呢?
用SQL 2005 支持的 WITH() 效率会不会高很多,若是的,考虑不用SQL 2000了,谢谢