declare @step int declare @maxlen intselect top 1 @maxlen=len(id) from 表 order by len(id) desc set rowcount @maxlen/3 select id=identity(int,1,1) into #t from sysobjects a,sysobjects b set rowcount 0select substring(a.id,1,b.id*3) from 表 a,#t b where b.id*3<=len(a.id)
朋友,阶梯不一定是3,还可以有这种情况:已知数: 111 111222333 aa aabbcc求: 111 111222 111222333 aa aabb aaabbcc也就是将中间一级补上。请各位看看如何SQL。
declare @maxlen intselect top 1 @maxlen=len(id) from 表 order by len(id) desc set rowcount @maxlen select id=identity(int,1,1) into #t from sysobjects a,sysobjects b set rowcount 0select substring(a.id,1,b.id) from 表 a,#t b where b.id<=len(a.id) and substring(a.id,b.id,1)<>substring(a.id,b.id-1,1)
修正declare @maxlen intselect top 1 @maxlen=len(a.id) from (select 'aaa' as id union all select 'aaabbccc' as id union all select '111' as id union all select '1112345' as id ) a order by len(a.id) descset rowcount @maxlen select id=identity(int,1,1) into #t from sysobjects a,sysobjects b set rowcount 0 select substring(a.id,1,b.id) from (select 'aaa' as id union all select 'aaabbccc' as id union all select '111' as id union all select '1112345' as id ) a,#t b where b.id<len(a.id) and substring(a.id,b.id,1)<>substring(a.id,b.id+1,1)
最后一个版本declare @maxlen intselect top 1 @maxlen=len(a.id) from (select 'aaa' as id union all select 'aaabbccc' as id union all select '111' as id union all select '1112345' as id ) a order by len(a.id) descset rowcount @maxlen select id=identity(int,1,1) into #t from sysobjects a,sysobjects b set rowcount 0 select substring(a.id,1,b.id) as c from (select 'aaa' as id union all select 'aaabbccc' as id union all select '111' as id union all select '1112345' as id ) a,#t b where b.id<len(a.id+'~') --这里指定一个特殊字符就行 and substring(a.id+'~',b.id,1)<>substring(a.id+'~',b.id+1,1) order by c
多谢,多谢啊!好像是不好实现,哥们儿我就是想要这个结果: 111 111222 111222333 aa aabb aaabbcc难为你们了 :)
我那个是构造的一个表这部分(select 'aaa' as id union all select 'aaabbccc' as id union all select '111' as id union all select '1112345' as id ) a你完全可以用你的表名来代替的.....
这下够明白了吧declare @maxlen intselect top 1 @maxlen=len(a.你的字段名) from 你的表名 a order by len(a.你的字段名) descset rowcount @maxlen select id=identity(int,1,1) into #t from sysobjects a,sysobjects b set rowcount 0 select substring(a.你的字段名,1,b.id) as c from 你的表名 a,#t b where b.id<len(a.你的字段名+'~') --这里指定一个特殊字符就行 and substring(a.你的字段名+'~',b.id,1)<>substring(a.你的字段名+'~',b.id+1,1) order by c
刚才我试了,结果中没有包含,最长的那一级数,怎么办?只有: 111 111222 aa aabb没有了: 111222333 aaabbcc
declare @maxlen intselect top 1 @maxlen=len(id) from 表 order by len(id) desc
set rowcount @maxlen/3
select id=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0select substring(a.id,1,b.id*3)
from 表 a,#t b
where b.id*3<=len(a.id)
111222333
aa
aabbcc求:
111
111222
111222333
aa
aabb
aaabbcc也就是将中间一级补上。请各位看看如何SQL。
set rowcount @maxlen
select id=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0select substring(a.id,1,b.id)
from 表 a,#t b
where b.id<=len(a.id)
and substring(a.id,b.id,1)<>substring(a.id,b.id-1,1)
(select 'aaa' as id union all
select 'aaabbccc' as id union all
select '111' as id union all
select '1112345' as id ) a
order by len(a.id) descset rowcount @maxlen
select id=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select substring(a.id,1,b.id)
from (select 'aaa' as id union all
select 'aaabbccc' as id union all
select '111' as id union all
select '1112345' as id ) a,#t b
where b.id<len(a.id)
and substring(a.id,b.id,1)<>substring(a.id,b.id+1,1)
111
1112
11123
111234
aaa
aaabb(所影响的行数为 6 行)后面应该加上一个排序
(select 'aaa' as id union all
select 'aaabbccc' as id union all
select '111' as id union all
select '1112345' as id ) a
order by len(a.id) descset rowcount @maxlen
select id=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select substring(a.id,1,b.id) as c
from (select 'aaa' as id union all
select 'aaabbccc' as id union all
select '111' as id union all
select '1112345' as id ) a,#t b
where b.id<len(a.id+'~') --这里指定一个特殊字符就行
and substring(a.id+'~',b.id,1)<>substring(a.id+'~',b.id+1,1)
order by c
111222
111222333
aa
aabb
aaabbcc难为你们了 :)
order by left(c,1),len(c)
AABBBCCCC我要取:AA
AABBB
AABBBCCCC
select 'aaabbccc' as id union all
select '111' as id union all
select '1112345' as id ) a你完全可以用你的表名来代替的.....
from 你的表名 a
order by len(a.你的字段名) descset rowcount @maxlen
select id=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select substring(a.你的字段名,1,b.id) as c
from 你的表名 a,#t b
where b.id<len(a.你的字段名+'~') --这里指定一个特殊字符就行
and substring(a.你的字段名+'~',b.id,1)<>substring(a.你的字段名+'~',b.id+1,1)
order by c
111
111222
aa
aabb没有了:
111222333
aaabbcc