[code=SQL] --sql2000,用了临时表,沒想太多,随手写跑了下看出了结果就贴上来了。 --因为感觉要出序号的话,2000不用临时表不怎么好处理 --只限定了3层 --如果层数不定的话,建议修改table schema,改成基本的树格式,可以利用深度查询来处理create table T (a1 varchar(10),a2 varchar(10),a3 varchar(10)) insert into T select 'Q','Q1','Q2' insert into T select 'Q','Q1','Q3' insert into T select 'Q','Q2','Q4' insert into T select 'Q','Q2','Q5' insert into T select 'P','P','P1' insert into T select 'P','P','P2' insert into T select 'P','P','P3' insert into T select 'P','P4','P5' insert into T select 'P','P4','P6'GOselect id=identity(int,1,1) ,* into #t from Tselect tmp1=(select count(distinct a1) from #t a where a.id<=#t.id), tmp2=(select count(distinct a2) from #t a where a.id<=#t.id and a.a1=#t.a1), tmp3=(select count(distinct a3) from #t a where a.id<=#t.id and a.a1=#t.a1 and a.a2=#t.a2), * into #tmp from #t select distinct rtrim(tmp1) as [tmp] , a1 from #tmp union all select distinct rtrim(tmp1)+'.'+rtrim(tmp2), a2 from #tmp union all select distinct rtrim(tmp1)+'.'+rtrim(tmp2)+'.'+rtrim(tmp3),a3 from #tmp order by tmp/* 1 Q 1.1 Q1 1.1.1 Q2 1.1.2 Q3 1.2 Q2 1.2.1 Q4 1.2.2 Q5 2 P 2.1 P 2.1.1 P1 2.1.2 P2 2.1.3 P3 2.2 P4 2.2.1 P5 2.2.2 P6*/ GO drop table T drop table #t,#tmp [/SQL]
--sql2000,用了临时表,沒想太多,随手写跑了下看出了结果就贴上来了。 --因为感觉要出序号的话,2000不用临时表不怎么好处理 --只限定了3层 --如果层数不定的话,建议修改table schema,改成基本的树格式,可以利用深度查询来处理 create table T (a1 varchar(10),a2 varchar(10),a3 varchar(10)) insert into T select 'Q','Q1','Q2' insert into T select 'Q','Q1','Q3' insert into T select 'Q','Q2','Q4' insert into T select 'Q','Q2','Q5' insert into T select 'P','P','P1' insert into T select 'P','P','P2' insert into T select 'P','P','P3' insert into T select 'P','P4','P5' insert into T select 'P','P4','P6' GO select id=identity(int,1,1) ,* into #t from T select tmp1=(select count(distinct a1) from #t a where a.id <=#t.id), tmp2=(select count(distinct a2) from #t a where a.id <=#t.id and a.a1=#t.a1), tmp3=(select count(distinct a3) from #t a where a.id <=#t.id and a.a1=#t.a1 and a.a2=#t.a2), * into #tmp from #t select distinct rtrim(tmp1) as [tmp] , a1 from #tmp union all select distinct rtrim(tmp1)+'.'+rtrim(tmp2), a2 from #tmp union all select distinct rtrim(tmp1)+'.'+rtrim(tmp2)+'.'+rtrim(tmp3),a3 from #tmp order by tmp /* 1 Q 1.1 Q1 1.1.1 Q2 1.1.2 Q3 1.2 Q2 1.2.1 Q4 1.2.2 Q5 2 P 2.1 P 2.1.1 P1 2.1.2 P2 2.1.3 P3 2.2 P4 2.2.1 P5 2.2.2 P6 */ GO drop table T drop table #t,#tmp
create table tb(a varchar(50),b varchar(50),c varchar(50)) insert into tb select 'Q','Q1','Q2' insert into tb select 'Q','Q1','Q3' insert into tb select 'Q','Q2','Q4' insert into tb select 'Q','Q2','Q5' insert into tb select 'P','P','P1' insert into tb select 'P','P','P2' insert into tb select 'P','P','P3' insert into tb select 'P','P4','P5' insert into tb select 'P','P4','P6'select * from ( select distinct a,p1=ltrim((select count(distinct a) from tb where a>=t.a)) from tb t union all select distinct b,ltrim((select count(distinct a) from tb where a>=t.a))+'.'+ltrim((select count(distinct b) from tb where a=t.a and b<=t.b)) from tb t union all select distinct c, ltrim((select count(distinct a) from tb where a>=t.a))+'.'+ltrim((select count(distinct b) from tb where a=t.a and b<=t.b))+'.'+ltrim((select count(distinct c) from tb where a=t.a and b=t.b and c<=t.c)) from tb t )t order by left(a,1) desc,len(a),p1a p1 Q 1 Q1 1.1 Q2 1.1.1 Q3 1.1.2 Q2 1.2 Q4 1.2.1 Q5 1.2.2 P 2 P 2.1 P1 2.1.1 P2 2.1.2 P3 2.1.3 P4 2.2 P5 2.2.1 P6 2.2.2
--sql2000,用了临时表,沒想太多,随手写跑了下看出了结果就贴上来了。
--因为感觉要出序号的话,2000不用临时表不怎么好处理
--只限定了3层
--如果层数不定的话,建议修改table schema,改成基本的树格式,可以利用深度查询来处理create table T (a1 varchar(10),a2 varchar(10),a3 varchar(10))
insert into T select 'Q','Q1','Q2'
insert into T select 'Q','Q1','Q3'
insert into T select 'Q','Q2','Q4'
insert into T select 'Q','Q2','Q5'
insert into T select 'P','P','P1'
insert into T select 'P','P','P2'
insert into T select 'P','P','P3'
insert into T select 'P','P4','P5'
insert into T select 'P','P4','P6'GOselect id=identity(int,1,1) ,* into #t from Tselect
tmp1=(select count(distinct a1) from #t a where a.id<=#t.id),
tmp2=(select count(distinct a2) from #t a where a.id<=#t.id and a.a1=#t.a1),
tmp3=(select count(distinct a3) from #t a where a.id<=#t.id and a.a1=#t.a1 and a.a2=#t.a2),
*
into #tmp
from #t
select distinct rtrim(tmp1) as [tmp] , a1
from #tmp
union all
select distinct rtrim(tmp1)+'.'+rtrim(tmp2), a2
from #tmp
union all
select distinct rtrim(tmp1)+'.'+rtrim(tmp2)+'.'+rtrim(tmp3),a3
from #tmp
order by tmp/*
1 Q
1.1 Q1
1.1.1 Q2
1.1.2 Q3
1.2 Q2
1.2.1 Q4
1.2.2 Q5
2 P
2.1 P
2.1.1 P1
2.1.2 P2
2.1.3 P3
2.2 P4
2.2.1 P5
2.2.2 P6*/
GO
drop table T
drop table #t,#tmp
[/SQL]
--sql2000,用了临时表,沒想太多,随手写跑了下看出了结果就贴上来了。
--因为感觉要出序号的话,2000不用临时表不怎么好处理
--只限定了3层
--如果层数不定的话,建议修改table schema,改成基本的树格式,可以利用深度查询来处理 create table T (a1 varchar(10),a2 varchar(10),a3 varchar(10))
insert into T select 'Q','Q1','Q2'
insert into T select 'Q','Q1','Q3'
insert into T select 'Q','Q2','Q4'
insert into T select 'Q','Q2','Q5'
insert into T select 'P','P','P1'
insert into T select 'P','P','P2'
insert into T select 'P','P','P3'
insert into T select 'P','P4','P5'
insert into T select 'P','P4','P6' GO select id=identity(int,1,1) ,* into #t from T select
tmp1=(select count(distinct a1) from #t a where a.id <=#t.id),
tmp2=(select count(distinct a2) from #t a where a.id <=#t.id and a.a1=#t.a1),
tmp3=(select count(distinct a3) from #t a where a.id <=#t.id and a.a1=#t.a1 and a.a2=#t.a2),
*
into #tmp
from #t
select distinct rtrim(tmp1) as [tmp] , a1
from #tmp
union all
select distinct rtrim(tmp1)+'.'+rtrim(tmp2), a2
from #tmp
union all
select distinct rtrim(tmp1)+'.'+rtrim(tmp2)+'.'+rtrim(tmp3),a3
from #tmp
order by tmp /*
1 Q
1.1 Q1
1.1.1 Q2
1.1.2 Q3
1.2 Q2
1.2.1 Q4
1.2.2 Q5
2 P
2.1 P
2.1.1 P1
2.1.2 P2
2.1.3 P3
2.2 P4
2.2.1 P5
2.2.2 P6 */
GO
drop table T
drop table #t,#tmp
insert into tb select 'Q','Q1','Q2'
insert into tb select 'Q','Q1','Q3'
insert into tb select 'Q','Q2','Q4'
insert into tb select 'Q','Q2','Q5'
insert into tb select 'P','P','P1'
insert into tb select 'P','P','P2'
insert into tb select 'P','P','P3'
insert into tb select 'P','P4','P5'
insert into tb select 'P','P4','P6'select * from (
select distinct a,p1=ltrim((select count(distinct a) from tb where a>=t.a)) from tb t
union all
select distinct b,ltrim((select count(distinct a) from tb where a>=t.a))+'.'+ltrim((select count(distinct b) from tb where a=t.a and b<=t.b)) from tb t
union all
select distinct c,
ltrim((select count(distinct a) from tb where a>=t.a))+'.'+ltrim((select count(distinct b) from tb where a=t.a and b<=t.b))+'.'+ltrim((select count(distinct c) from tb where a=t.a and b=t.b and c<=t.c)) from tb t
)t
order by left(a,1) desc,len(a),p1a p1
Q 1
Q1 1.1
Q2 1.1.1
Q3 1.1.2
Q2 1.2
Q4 1.2.1
Q5 1.2.2
P 2
P 2.1
P1 2.1.1
P2 2.1.2
P3 2.1.3
P4 2.2
P5 2.2.1
P6 2.2.2
P
A出來會是
2
3
1
或者
2
1
3