--测试数据
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_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
解决方案 »
- sql server 2005 如何把数据从SQL Server里取出,放在内存里,以加快查询速度?
- 求教SQL语句,具体见帖子内容,谢谢。
- SQL技术中对于年龄的定义类型是用int还是用datetime?
- 在笔记本电脑安装SQL出现错误: command line option syntax error!type command /? for help
- 视图主键?
- 求一SQL语句,关于记录更新
- 关系型数据库中有哪些原子操作?
- 请教:日期的判断?
- 拜托高手,给个存储过程或参考例子,如何直接把access数据库表中的记录导入到sql中?
- SQL Sever2016
- 我的一个WEB项目,现在是SQL Server数据库,想切换到Oracle数据库,咋整?
- 如何获取identity产生的空缺
贴一个给你参考:
--BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION ALL SELECT 2,N'B产品',0.02
UNION ALL SELECT 3,N'C产品',0.10
UNION ALL SELECT 4,N'D配件',0.15
UNION ALL SELECT 5,N'E物料',0.03
UNION ALL SELECT 6,N'F物料',0.01
UNION ALL SELECT 7,N'G配件',0.02CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION ALL SELECT 1,7 --A产品由D配件和G配件组成
UNION ALL SELECT 2,1
UNION ALL SELECT 2,6
UNION ALL SELECT 2,7 --B产品由F物料及G配件组成
UNION ALL SELECT 4,5
UNION ALL SELECT 4,6 --D配件由F物料组成
UNION ALL SELECT 3,2
UNION ALL SELECT 3,1 --C产品由A产品和B产品组成
GOCREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num int --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROM Bom a,Item b
WHERE a.ChildId=b.ID
AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM @t a,Bom b,Item c
WHERE a.ChildId=b.ItemID
AND b.ChildId=c.ID
AND a.Level=@Level-1
END
RETURN
END
GO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId/*
ItemID ItemName ChildId ChildName Nums Level
----------- ---------- ----------- ---------- ----------- -----------
1 A产品 4 D配件 12 1
1 A产品 7 G配件 10 1
1 A产品 5 E物料 12 2
1 A产品 6 F物料 12 2
2 B产品 1 A产品 10 1
2 B产品 6 F物料 10 1
2 B产品 7 G配件 10 1
2 B产品 4 D配件 12 2
2 B产品 7 G配件 10 2
2 B产品 5 E物料 12 3
2 B产品 6 F物料 12 3
3 C产品 1 A产品 10 1
3 C产品 2 B产品 10 1
3 C产品 1 A产品 10 2
3 C产品 4 D配件 12 2
3 C产品 6 F物料 10 2
3 C产品 7 G配件 10 2
3 C产品 7 G配件 10 2
3 C产品 4 D配件 12 3
3 C产品 5 E物料 12 3
3 C产品 6 F物料 12 3
3 C产品 7 G配件 10 3
3 C产品 5 E物料 12 4
3 C产品 6 F物料 12 4(24 row(s) affected)
*/
drop table item
drop table bom
drop function f_Bom
-----------------------------
001 00101 Rec0
002 00201 Rec02
003 00301 Rec03
00101 0010101 Rec04
00101 0010102 Rec05
00201 0020101 Rec6
00201 0020102 Rec7
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11————————————————————————
第一个想得到的结果:想得到的结果:输入Item=001
Item DItem Des
-----------------------------
001 00101 Rec0
00101 0010101 Rec04
00101 0010102 Rec05
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11
想得到的结果:输入Item=002
Item DItem Des
-----------------------------
002 00201 Rec02
00201 0020101 Rec6
00201 0020102 Rec7想得到的结果:输入Item=003
Item DItem Des
-----------------------------
003 00301 Rec03
————————————————————×××××××××××××××————————————————————————
第二个想得到的结果:想得到的结果:输入DItem=00101
Item DItem Des
-----------------------------
001 00101 Rec0
00101 0010101 Rec04
00101 0010102 Rec05
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11
想得到的结果:输入DItem=00201
Item DItem Des
-----------------------------
002 00201 Rec02
00201 0020101 Rec6
00201 0020102 Rec7想得到的结果:输入DItem=00301
Item DItem Des
-----------------------------
003 00301 Rec03
————————————————————我是初学SQL语法的,能否根据上述内容,帮帮忙看看应该如何写代码
我用的前端开发工具是VB,谢谢了
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO这个函数就可以了!!
请您再看看我的具体要求,非常感谢
Item DItem Des
-----------------------------
001 00101 Rec0
002 00201 Rec02
003 00301 Rec03
00101 0010101 Rec04
00101 0010102 Rec05
00201 0020101 Rec6
00201 0020102 Rec7
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11 ————————————————————————
第一个想得到的结果: 想得到的结果:输入Item=001
Item DItem Des
-----------------------------
001 00101 Rec0
00101 0010101 Rec04
00101 0010102 Rec05
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11
想得到的结果:输入Item=002
Item DItem Des
-----------------------------
002 00201 Rec02
00201 0020101 Rec6
00201 0020102 Rec7 想得到的结果:输入Item=003
Item DItem Des
-----------------------------
003 00301 Rec03
———————————————————— ××××××××××××××× ————————————————————————
第二个想得到的结果: 想得到的结果:输入DItem=00101
Item DItem Des
-----------------------------
001 00101 Rec0
00101 0010101 Rec04
00101 0010102 Rec05
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11
想得到的结果:输入DItem=00201
Item DItem Des
-----------------------------
002 00201 Rec02
00201 0020101 Rec6
00201 0020102 Rec7 想得到的结果:输入DItem=00301
Item DItem Des
-----------------------------
003 00301 Rec03
———————————————————— 我是初学SQL语法的,能否根据上述内容,帮帮忙看看应该如何写代码
我用的前端开发工具是VB,谢谢了
-->生成测试数据
declare @s nvarchar(10)
set @s ='001'
declare @tb table([Item] nvarchar(7),[DItem] nvarchar(9),[Des] nvarchar(5))
Insert @tb
select N'001',N'00101',N'Rec0' union all
select N'002',N'00201',N'Rec02' union all
select N'003',N'00301',N'Rec03' union all
select N'00101',N'0010101',N'Rec04' union all
select N'00101',N'0010102',N'Rec05' union all
select N'00201',N'0020101',N'Rec6' union all
select N'00201',N'0020102',N'Rec7' union all
select N'0010101',N'001010101',N'Rec08' union all
select N'0010101',N'001010102',N'Rec09' union all
select N'0010102',N'001010201',N'Rec10' union all
select N'0010102',N'001010202',N'Rec11'
select [Item],[DItem],[Des]
from
(
Select distinct a.[Item],a.[DItem],a.[Des],b.[Item] as [ItemX]from @tb a
join @tb b on a.[item] like b.[item]+'%'
) t
where t.[ItemX] =left(@s,3)
/*
Item DItem Des
------- --------- -----
001 00101 Rec0
00101 0010101 Rec04
00101 0010102 Rec05
0010101 001010101 Rec08
0010101 001010102 Rec09
0010102 001010201 Rec10
0010102 001010202 Rec11
*/
set @s ='00201'
/*
Item DItem Des
------- --------- -----
002 00201 Rec02
00201 0020101 Rec6
00201 0020102 Rec7
*/
set @s ='00301'
/*
Item DItem Des
------- --------- -----
003 00301 Rec03
*/