27,579
社区成员
发帖
与我相关
我的任务
分享求一条SQL语句，将BOM按层次结构显示。
原表数据，
（有一个BOM，所有的成品，半成品都放在一张表里面，在这里没有层次区分，所有的数据都在这张表中）
成品编号 BOMID 行号 物料编码 用量 类型
A-001 BOM-1 1 1001 1 物料
A-001 BOM-1 2 1003 3 半成品
A-001 BOM-1 3 1004 2 半成品
A-001 BOM-1 4 1005 1 物料
A-001 BOM-1 5 1006 2 物料
B-001 BOM-2 1 1001 1 物料
B-001 BOM-2 2 1003 5 半成品
B-001 BOM-2 3 1009 2 物料
1003 BOM-32 1 3301 0.3 半成品
1003 BOM-32 2 3302 3 物料
1003 BOM-32 3 3303 2 物料
3301 BOM43 1 98001 物料
3301 BOM43 2 98001 物料
3301 BOM43 3 98001 物料
3301 BOM43 4 98001 物料
3301 BOM43 5 98001 物料
怎样将上表的数据变成树状结构的下图所示的，（这个表里面很多BOM，是否能一次性将所有的BOM按下列的方式树状方式显示出来。）
成品编号 层次 BOMID 行号 物料编码 用量 类型
A-001 1 BOM-1 1 1001 1 物料
A-001 1 BOM-1 2 1003 3 半成品
A-001 1.1 BOM-32 1 3301 0.3 半成品
A-001 1.1.1 BOM43 1 98001 物料
A-001 1.1.2 BOM43 2 98001 物料
A-001 1.1.3 BOM43 3 98001 物料
A-001 1.1.4 BOM43 4 98001 物料
A-001 1.1.5 BOM43 5 98001 物料
A-001 1.2 BOM-32 2 3302 3 物料
A-001 1.3 BOM-32 3 3303 2 物料
A-001 1 BOM-1 3 1004 2 半成品
A-001 1 BOM-1 4 1005 1 物料
A-001 1 BOM-1 5 1006 2 物料
...全文
请发表友善的回复…
发表回复
dawugui 2011-03-16
- 打赏
- 举报
/*
标题：SQL SERVER 2005中树结构按照各顶级节点排序
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-10-24
地点：陕西西安
说明：
如下树级结构中需要按照各顶级节点排序
id pid name
---- ---- ----------
001 000 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
011 000 江西省
012 011 南昌市
013 012 南昌县
014 012 新建县
015 012 进贤县
016 012 安义县
017 013 麻丘镇
018 011 九江市
需要的结果：
id name pid
---- ---------- ----
001 广东省 000
002 广州市 001
004 天河区 002
003 深圳市 001
005 罗湖区 003
006 福田区 003
007 宝安区 003
008 西乡镇 007
009 龙华镇 007
010 松岗镇 007
011 江西省 000
012 南昌市 011
013 南昌县 012
017 麻丘镇 013
014 新建县 012
015 进贤县 012
016 安义县 012
018 九江市 011
*/
create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , '000' , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
insert into tb values('011' , '000' , N'江西省')
insert into tb values('012' , '011' , N'南昌市')
insert into tb values('013' , '012' , N'南昌县')
insert into tb values('014' , '012' , N'新建县')
insert into tb values('015' , '012' , N'进贤县')
insert into tb values('016' , '012' , N'安义县')
insert into tb values('017' , '013' , N'麻丘镇')
insert into tb values('018' , '011' , N'九江市')
go
;with t as
(
select px = cast(id as varchar(500)) , * from tb t
where not exists(select 1 from tb where id = t.pid)
union all
select cast(b.px + ',' + a.id as varchar(500)) , a.*
from tb a , t b where a.pid = b.id
)
select id , name , pid from t order by px
drop table tb
pt1314917 2011-03-16
- 打赏
- 举报
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (成品编号 varchar(5),BOMID varchar(6),行号 int,物料编码 int,用量 numeric(2,1),类型 varchar(6))
insert into [tb]
select 'A-001','BOM-1',1,1001,1,'物料' union all
select 'A-001','BOM-1',2,1003,3,'半成品' union all
select 'A-001','BOM-1',3,1004,2,'半成品' union all
select 'A-001','BOM-1',4,1005,1,'物料' union all
select 'A-001','BOM-1',5,1006,2,'物料' union all
select 'B-001','BOM-2',1,1001,1,'物料' union all
select 'B-001','BOM-2',2,1003,5,'半成品' union all
select 'B-001','BOM-2',3,1009,2,'物料' union all
select '1003','BOM-32',1,3301,0.3,'半成品' union all
select '1003','BOM-32',2,3302,3,'物料' union all
select '1003','BOM-32',3,3303,2,'物料' union all
select '3301','BOM43',1,98001,null,'物料' union all
select '3301','BOM43',2,98001,null,'物料' union all
select '3301','BOM43',3,98001,null,'物料' union all
select '3301','BOM43',4,98001,null,'物料' union all
select '3301','BOM43',5,98001,null,'物料'
select * from [tb]
;with wsp
as
(
select 成品编号,层次=cast(1 as varchar),px=cast(行号 as varchar),BOMID,行号,物料编码,用量,类型 from tb b
where not exists(select 1 from tb where ltrim(物料编码)=b.成品编号) and 成品编号='A-001'
union all
select a.成品编号,层次=cast(a.层次+'.'+ltrim(row_number() over(order by b.行号)) as varchar),
px=cast(a.px+'.'+ltrim(row_number() over(order by b.行号)) as varchar),b.BOMID,b.行号,b.物料编码,b.用量,b.类型
from wsp a,tb b where ltrim(a.物料编码)=b.成品编号
)
select 成品编号,层次,BOMID,行号,物料编码,用量,类型 from wsp order by px
--结果：
成品编号 BOMID 行号 物料编码 用量 类型
----- ------ ----------- ----------- --------------------------------------- ------
A-001 BOM-1 1 1001 1.0 物料
A-001 BOM-1 2 1003 3.0 半成品
A-001 BOM-1 3 1004 2.0 半成品
A-001 BOM-1 4 1005 1.0 物料
A-001 BOM-1 5 1006 2.0 物料
B-001 BOM-2 1 1001 1.0 物料
B-001 BOM-2 2 1003 5.0 半成品
B-001 BOM-2 3 1009 2.0 物料
1003 BOM-32 1 3301 0.3 半成品
1003 BOM-32 2 3302 3.0 物料
1003 BOM-32 3 3303 2.0 物料
3301 BOM43 1 98001 NULL 物料
3301 BOM43 2 98001 NULL 物料
3301 BOM43 3 98001 NULL 物料
3301 BOM43 4 98001 NULL 物料
3301 BOM43 5 98001 NULL 物料
(16 行受影响)
成品编号 层次 BOMID 行号 物料编码 用量 类型
----- ------------------------------ ------ ----------- ----------- --------------------------------------- ------
A-001 1 BOM-1 1 1001 1.0 物料
A-001 1 BOM-1 2 1003 3.0 半成品
A-001 1.1 BOM-32 1 3301 0.3 半成品
A-001 1.1.1 BOM43 1 98001 NULL 物料
A-001 1.1.2 BOM43 2 98001 NULL 物料
A-001 1.1.3 BOM43 3 98001 NULL 物料
A-001 1.1.4 BOM43 4 98001 NULL 物料
A-001 1.1.5 BOM43 5 98001 NULL 物料
A-001 1.2 BOM-32 2 3302 3.0 物料
A-001 1.3 BOM-32 3 3303 2.0 物料
A-001 1 BOM-1 3 1004 2.0 半成品
A-001 1 BOM-1 4 1005 1.0 物料
A-001 1 BOM-1 5 1006 2.0 物料
--小F-- 2011-03-16
- 打赏
- 举报
--参考一下实例
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20))
INSERT [tb]
SELECT N'A','01' UNION ALL
SELECT N'B','01.01' UNION ALL
SELECT N'C','01.01.01' UNION ALL
SELECT N'F','01.01.01.01' UNION ALL
SELECT N'E','01.01.01.02' UNION ALL
SELECT N'D','01.01.01.03' UNION ALL
SELECT N'O','02' UNION ALL
SELECT N'P','02.01' UNION ALL
SELECT N'Q','02.01.01'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
---另一种方法
;WITH T AS
(
SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A
WHERE NOT EXISTS(
SELECT 1 FROM TB
WHERE A.COL2 LIKE COL2+'%'
AND LEN(A.COL2)>LEN(COL2))
UNION ALL
SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000))
FROM TB A
JOIN T B
ON A.COL2 LIKE B.COL2+'%'
AND LEN(A.COL2)-3=LEN(B.COL2)
)
SELECT * FROM T ORDER BY LEFT(COL2,2)
/*
GUID COL1 COL2 PATH
----------- ---------- -------------------- --------------------
1 A 01 A
2 B 01.01 A-->B
3 C 01.01.01 A-->B-->C
4 F 01.01.01.01 A-->B-->C-->F
5 E 01.01.01.02 A-->B-->C-->E
6 D 01.01.01.03 A-->B-->C-->D
7 O 02 O
8 P 02.01 O-->P
9 Q 02.01.01 O-->P-->Q
(9 行受影响)
*/
;WITH T AS
(
SELECT *,CAST(COL1 AS VARCHAR(1000)) AS PATH
FROM TB
WHERE COL2 NOT LIKE '%.%'
UNION ALL
SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000))
FROM TB A,T B
WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]'
)
SELECT * FROM T
ORDER BY LEFT(COL2,2)
/*
GUID COL1 COL2 PATH
----------- ---------- -------------------- --------------------
1 A 01 A
2 B 01.01 A-->B
3 C 01.01.01 A-->B-->C
4 F 01.01.01.01 A-->B-->C-->F
5 E 01.01.01.02 A-->B-->C-->E
6 D 01.01.01.03 A-->B-->C-->D
7 O 02 O
8 P 02.01 O-->P
9 Q 02.01.01 O-->P-->Q
(9 行受影响)
*/--小F-- 2011-03-16
- 打赏
- 举报
参考:
BOM按节点排序应用实例
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-23 02:37:28
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Subject: BOM按节点排序应用实例
--------------------------------------------------------------------------
--实例1:
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))
INSERT [tb]
SELECT 1,'01',0,N'服装' UNION ALL
SELECT 2,'01',1,N'男装' UNION ALL
SELECT 3,'01',2,N'西装' UNION ALL
SELECT 4,'01',3,N'全毛' UNION ALL
SELECT 5,'02',3,N'化纤' UNION ALL
SELECT 6,'02',2,N'休闲装' UNION ALL
SELECT 7,'02',1,N'女装' UNION ALL
SELECT 8,'01',7,N'套装' UNION ALL
SELECT 9,'02',7,N'职业装' UNION ALL
SELECT 10,'03',7,N'休闲装' UNION ALL
SELECT 11,'04',7,N'西装' UNION ALL
SELECT 12,'01',11,N'全毛' UNION ALL
SELECT 13,'02',11,N'化纤' UNION ALL
SELECT 14,'05',7,N'休闲装'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH T AS
(
SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,
CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)
UNION ALL
SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,
CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.pid=B.id
)
SELECT Code,Name FROM T
ORDER BY px
/*
Code Name
-------------------- ----------
01 服装
0101 男装
010101 西装
01010101 全毛
01010102 化纤
010102 休闲装
0102 女装
010201 套装
010202 职业装
010203 休闲装
010204 西装
01020401 全毛
01020402 化纤
010205 休闲装
(14 行受影响)
*/
--实例2:
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1
(6 行受影响)
*/
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx