create table tree
(
id int identity(1,1),
yiji VARCHAR(50),
erji varchar(50),
sanji varchar(50)
)
insert into tree values('蔬菜','黄瓜','番茄')
insert into tree values('蔬菜','黄瓜','鸡毛菜')
insert into tree values('蔬菜','黄瓜','豆角')
insert into tree values('蔬菜','南瓜','丝瓜')
insert into tree values('水果','苹果','梨')
insert into tree values('衣服','上衣','短裤')
我要的结果是:1.蔬菜
2.黄瓜
3.番茄
3.鸡毛菜
3.豆角
2.南瓜
3.丝瓜
1.水果
2.苹果
3.梨
1.衣服
2.上衣
3.短裤
(
id int identity(1,1),
yiji VARCHAR(50),
erji varchar(50),
sanji varchar(50)
)
insert into tree values('蔬菜','黄瓜','番茄')
insert into tree values('蔬菜','黄瓜','鸡毛菜')
insert into tree values('蔬菜','黄瓜','豆角')
insert into tree values('蔬菜','南瓜','丝瓜')
insert into tree values('水果','苹果','梨')
insert into tree values('衣服','上衣','短裤')
我要的结果是:1.蔬菜
2.黄瓜
3.番茄
3.鸡毛菜
3.豆角
2.南瓜
3.丝瓜
1.水果
2.苹果
3.梨
1.衣服
2.上衣
3.短裤
(
id int identity(1,1),
yiji VARCHAR(50),
erji varchar(50),
sanji varchar(50)
)
insert into #tree values('蔬菜','黄瓜','番茄')
insert into #tree values('蔬菜','黄瓜','鸡毛菜')
insert into #tree values('蔬菜','黄瓜','豆角')
insert into #tree values('蔬菜','南瓜','丝瓜')
insert into #tree values('水果','苹果','梨')
insert into #tree values('衣服','上衣','短裤')select * from #tree
;with cte as
(select DENSE_RANK() over(order by yiji) rid,DENSE_RANK() over(order by erji) cid,* from #tree
)
select * from (
select right('00'+CAST(rid as varchar(10)),2) as id,'-1' as pid ,yiji from (select distinct rid,yiji from cte) a
union all select right('00'+CAST(rid as varchar(10)),2)+right('00'+CAST(cid as varchar(10)),2) as id ,right('00'+CAST(rid as varchar(10)),2) pid,erji from (select distinct rid,cid,erji from cte) a
union select right('00'+CAST(rid as varchar(10)),2)+right('00'+CAST(cid as varchar(10)),2)+right('00'+CAST(id as varchar(10)),2) as id ,right('00'+CAST(rid as varchar(10)),2)+right('00'+CAST(cid as varchar(10)),2) pid,sanji from (select rid,cid,id,sanji from cte) a
) a order by 1
declare @test table
(
id int identity(1,1),
yiji nvarchar(50),
erji nvarchar(50),
sanji nvarchar(50)
)
insert into @test
select N'蔬菜',N'黄瓜',N'番茄' union all
select N'蔬菜',N'黄瓜',N'鸡毛菜' union all
select N'蔬菜',N'黄瓜',N'豆角' union all
select N'蔬菜',N'南瓜',N'丝瓜' union all
select N'水果',N'苹果',N'梨' union all
select N'衣服',N'上衣',N'短裤'
;with cte as
(
select row_number() over(partition by yiji order by sort) rn,* from
(
select id,yiji,ltrim(id)+'a1' sort from @test
union all
select id,erji,ltrim(id)+'b2' from @test
union all
select id,sanji,ltrim(id)+'c3' from @test
)t
)
select right(sort,1)+'.'+yiji from cte
where rn=1
order by sort
/*
----------------------------------------------------
1.蔬菜
2.黄瓜
3.番茄
3.鸡毛菜
3.豆角
2.南瓜
3.丝瓜
1.水果
2.苹果
3.梨
1.衣服
2.上衣
3.短裤
*/
(
id int identity(1,1),
yiji VARCHAR(50),
erji varchar(50),
sanji varchar(50),
re varchar(100)
)
insert into tree values('蔬菜','黄瓜','番茄','1111')
insert into tree values('蔬菜','黄瓜','鸡毛菜','2222')
insert into tree values('蔬菜','黄瓜','豆角','')
insert into tree values('蔬菜','南瓜','丝瓜','333')
insert into tree values('水果','苹果','梨','4444')
insert into tree values('衣服','上衣','短裤','555')
我要的结果是:----后面的是另外加的一列值备注,与第三级对应的,如果没有第三级备注就显示在第二级的后面
1.蔬菜
2.黄瓜
3.番茄 ----1111
3.鸡毛菜---2222
3.豆角---null
2.南瓜
3.丝瓜---333
1.水果
2.苹果
3.梨 ---444
1.衣服
2.上衣
3.短裤 ---555
(
id int identity(1,1),
yiji VARCHAR(50),
erji varchar(50),
sanji varchar(50),
re varchar(100)
)
insert into #tree values('蔬菜','黄瓜','番茄','1111')
insert into #tree values('蔬菜','黄瓜','鸡毛菜','2222')
insert into #tree values('蔬菜','黄瓜','豆角',null)
insert into #tree values('蔬菜','南瓜','丝瓜','333')
insert into #tree values('水果','苹果','梨','4444')
insert into #tree values('衣服','上衣','短裤','555')select * from #tree
;with cte as
(select DENSE_RANK() over(order by yiji) rid,DENSE_RANK() over(order by erji) cid,* from #tree
)
select id,pid,yiji,case when LEN(id)=6 then re else '' end as re from (
select right('00'+CAST(rid as varchar(10)),2) as id,'-1' as pid ,yiji,re from (select distinct rid,yiji,re from cte) a
union all select right('00'+CAST(rid as varchar(10)),2)+right('00'+CAST(cid as varchar(10)),2) as id ,right('00'+CAST(rid as varchar(10)),2) pid,erji,re from (select distinct rid,cid,erji,re from cte) a
union select right('00'+CAST(rid as varchar(10)),2)+right('00'+CAST(cid as varchar(10)),2)+right('00'+CAST(id as varchar(10)),2) as id ,right('00'+CAST(rid as varchar(10)),2)+right('00'+CAST(cid as varchar(10)),2) pid,sanji,re from (select rid,cid,id,sanji,re from cte) a
) a order by 1
(
id int identity(1,1),
yiji VARCHAR(50),
erji varchar(50),
sanji varchar(50),
re varchar(100)
ranks varchar(100)
)
insert into #tree values('蔬菜','黄瓜','番茄','1111','001')
insert into #tree values('蔬菜','黄瓜','鸡毛菜','2222','002')
insert into #tree values('蔬菜','黄瓜',' ','666','003')
insert into #tree values('蔬菜','南瓜','丝瓜','333','003')
insert into #tree values('水果','苹果','梨','4444','004')
insert into #tree values('衣服','上衣','短裤','555','005')如果我现在多加了一列标记ranks,现在要根据这个标记分类分层展示成这种效果怎么改.001
1蔬菜
2黄瓜
3番茄 111
002
1蔬菜
2黄瓜
3鸡毛菜 222
003
1蔬菜
2黄瓜 666
2南瓜
3丝瓜 333
004
1水果
2苹果
3梨 444
005
1衣服
2上衣
3短裤 555
如果我现在多加了一列标记ranks,现在要根据这个标记分类分层展示成这种效果怎么改.如果我现在多加了一列标记ranks,现在要根据这个标记分类分层展示成这种效果怎么改.如果我现在多加了一列标记ranks,现在要根据这个标记分类分层展示成这种效果怎么改.'渐行渐远'看到了请麻烦给我回一下哦,万分感激 !^_^^_^^_^