求助一个生成树形目录的SQL语句或者过程
现有表的结构是如此 prodsname,prodname
女戒,钻石女戒
女戒，豪华女戒
男戒，钻石男戒
男戒,豪华男戒
男戒,特殊男戒
吊坠,钻石吊坠
要求生成一个表ID,FATHERID,PRODNAME
1,0,女戒
2,0,男戒
3,0,吊坠
4,1,钻石女戒
5,1,豪华女戒
6,2，钻石男戒
7,2,豪华男戒
8,2,特殊男戒
9,3,钻石吊坠
------解决方案--------------------
- SQL code
create table #t (id int identity(1,1) ,pid int, v varchar(10))godeclare @t table(v1 varchar(10),v2 varchar(10));insert into @t select '女戒','钻石女戒' union allselect '女戒','豪华女戒' union allselect '男戒','钻石男戒';select * from @t;insert into #t (pid,v) select 0,v1 from (select distinct v1 from @t) x;insert into #t (pid,v) select a.id,b.v2 from #t a join @t b on b.v2 like '%'+a.vselect * from #t;drop table #t;/*id pid v----------- ----------- ----------1 0 男戒2 0 女戒3 1 钻石男戒4 2 钻石女戒5 2 豪华女戒*/
------解决方案--------------------
- SQL code
create table tb(prodsname nvarchar(10),prodname nvarchar(10))insert into tb select '女戒','钻石女戒'insert into tb select '女戒','豪华女戒'insert into tb select '男戒','钻石男戒'insert into tb select '男戒','豪华男戒'insert into tb select '男戒','特殊男戒'insert into tb select '吊坠','钻石吊坠'goselect row_number()over(order by (select 1))id,* into # from (select distinct 0 as pid,prodsname from tb)tinsert into #select row_number()over(order by(select 1))+(select max(id) from #),a.id,b.prodname from # a inner join tb b on a.prodsname=b.prodsnameselect * from # order by id/*id pid prodsname-------------------- ----------- ----------1 0 吊坠2 0 男戒3 0 女戒4 1 钻石吊坠5 2 钻石男戒6 2 豪华男戒7 2 特殊男戒8 3 钻石女戒9 3 豪华女戒(9 行受影响)*/godrop table tb,#