34,590
社区成员
发帖
与我相关
我的任务
分享
create table #t
(
eqc_id int,
epc_name varchar(20),
epc_parent_id int
)
insert into #t
select 1,'飞机',0 union all
select 2,'女装',0 union all
select 3,'男装',0 union all
select 4,'鞋子',0 union all
select 5,'箱包',0 union all
select 6,'垂直',1 union all
select 7,'滑跑',1 union all
select 8,'直升机',6 union all
select 9,'滑翔机',7 union all
select 10,'三角翼',7 union all
select 11,'仿真机',7 union all
select 12,'战斗机',7 union all
select 13,'旋翼机',7 union all
select 14,'上装',2 union all
select 15,'下装',2 union all
select 16,'裙子',2 union all
select 17,'袜子',2 union all
select 18,'上装',3 union all
select 19,'下装',3 union all
select 20,'袜子',3
;with cte as
(
select eqc_id,epc_name,epc_parent_id,1 as leve
from #t
where eqc_id=1
union all
select a.eqc_id,a.epc_name,a.epc_parent_id,b.leve+1 as leve
from #t a
join cte b on a.epc_parent_id=b.eqc_id
)
select * from cte
/*
eqc_id epc_name epc_parent_id leve
----------- -------------------- ------------- -----------
1 飞机 0 1
6 垂直 1 2
7 滑跑 1 2
9 滑翔机 7 3
10 三角翼 7 3
11 仿真机 7 3
12 战斗机 7 3
13 旋翼机 7 3
8 直升机 6 3
(9 行受影响)
*/
if OBJECT_ID('product_category') is not null
drop table product_category
go
create table product_category(
eqc_id int,
epc_name varchar(20),
epc_parent_id int
)
insert into product_category(eqc_id,epc_name, epc_parent_id)
select 1 , '飞机 ', 0 union all
select 2 , '女装 ', 0 union all
select 3 , '男装 ', 0 union all
select 4 , '鞋子 ', 0 union all
select 5 , '箱包 ', 0 union all
select 6 , '垂直 ', 1 union all
select 7 , '滑跑 ', 1 union all
select 8 , '直升机', 6 union all
select 9 , '滑翔机', 7 union all
select 10, '三角翼', 7 union all
select 11, '仿真机', 7 union all
select 12, '战斗机', 7 union all
select 13, '旋翼机', 7 union all
select 14, '上装 ', 2 union all
select 15, '下装 ', 2 union all
select 16, '裙子 ', 2 union all
select 17, '袜子 ', 2 union all
select 18, '上装 ', 3 union all
select 19, '下装 ', 3 union all
select 20, '袜子 ', 3
go
DECLARE @LevelTable TABLE(
ID INT,
[Level] INT,
Sort VARCHAR(50))
DECLARE @Level INT
SET @Level=0
INSERT @LevelTable
SELECT eqc_id, @Level, CONVERT(VARCHAR, eqc_id)
FROM product_category as A
WHERE A.epc_parent_id= 0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @LevelTable
SELECT A.eqc_id, @Level, B.Sort + CONVERT(VARCHAR, A.eqc_id)
FROM product_category A, @LevelTable B
WHERE A.epc_parent_id = B.ID AND B.[Level] = @Level - 1
ORDER BY A.eqc_id
END
--显示结果
SELECT A.eqc_id, A.epc_parent_id, Replicate('-', B.[Level]*4) + A.epc_name Name, B.Sort
FROM product_category A, @LevelTable B
WHERE A.eqc_id=B.ID
ORDER BY B.Sort