请交各位高手,一个超难的T-SQL语句写法

CHRL 2008-02-29 08:25:39
请帮忙按下面的要求排序:

f001
------
1
2
3
1.1
2.1
2.2
3.1
3.2
20.1

排序好后:
f001
------
1
1.1
2
2.1
2.2
3
3.1
3.2
20.1
...全文
633 59 打赏 收藏 转发到动态 举报
写回复
用AI写文章
59 条回复
切换为时间正序
请发表友善的回复…
发表回复
miaoyuanyan 2008-03-04
  • 打赏
  • 举报
回复
好复杂啊,学习!!!
wjlsmail 2008-03-04
  • 打赏
  • 举报
回复
Mark
wjlsmail 2008-03-04
  • 打赏
  • 举报
回复
Mark
treeyh 2008-03-03
  • 打赏
  • 举报
回复
不错,收藏下
-晴天 2008-03-02
  • 打赏
  • 举报
回复
declare @t table(f1 char(20), f2 char(20), c1 int, c2 int, c3 int, c4 int, c5 int)
insert into @t(f1) values('1')
insert into @t(f1) values('1.1')
insert into @t(f1) values('1.2')
insert into @t(f1) values('1.12')
insert into @t(f1) values('1.6.3')
insert into @t(f1) values('1.4.5')
insert into @t(f1) values('1.6.14')
insert into @t(f1) values('1.4.4.2')
insert into @t(f1) values('2.4.4.10')
insert into @t(f1) values('1.3.2.1.5')
insert into @t(f1) values('3')
insert into @t(f1) values('3.3')
insert into @t(f1) values('4.2')
insert into @t(f1) values('12.1.9')
insert into @t(f1) values('8.1')

update @t set c1=left(replace(f1,'.',space(20)),20)
update @t set f2=right(f1,19-len(ltrim(str(c1))))
update @t set c2=left(replace(f2,'.',space(20)),20)
update @t set f2=right(f2,19-len(ltrim(str(c2))))
update @t set c3=left(replace(f2,'.',space(20)),20)
update @t set f2=right(f2,19-len(ltrim(str(c2))))
update @t set c4=left(replace(f2,'.',space(20)),20)
update @t set f2=right(f2,19-len(ltrim(str(c2))))
update @t set c5=f2

select f1 from @t order by c1,c2,c3,c4,c5
blackant2 2008-03-01
  • 打赏
  • 举报
回复
虽然帖子结了,但这样热闹,我也来说说我的思路,其实用while就可以完成这个任务
declare @T table (f001 varchar(20))
insert into @T
select '1' union all
select '2' union all
select '3' union all
select '1.1' union all
select '2.3.10.10.13' union all
select '2.3.10.10.12' union all
select '2.3.10.10.11' union all
select '3.1' union all
select '3.2' union all
select '20.1' union all
select '1.1.2' union all
select '1.1.1' union all
select '1.1.3'


--定义要补0的长度
declare @length int
declare @index int
set @length=10
set @index=0

--加入排序字段后加入临时表
select f001,convert(nvarchar(4000),f001) as orderby into #tmp from @t

--更新排序
update #tmp
set orderby=(case charindex('.',f001) when 0 then REPLICATE('0',@length-len(f001))+f001
else REPLICATE('0',@length-charindex('.',f001)+1)+f001
end)

--循环补位,直到没有记录需要补位为止
while @@rowcount>0
begin
set @index=@index+1
update #tmp
set orderby=case charindex('.',orderby,@index*@length+2) when 0 then substring(orderby,1,@index*@length)+REPLICATE('0',@length*(@index+1)-len(orderby)+1)+substring(orderby,@index*@length+2,4000)
else substring(orderby,1,@index*@length)+REPLICATE('0',@length*(@index+1)-charindex('.',orderby,@index*@length+2)+2)+substring(orderby,@index*@length+2,4000) end
where charindex('.',orderby)>0

end

select * from #tmp order by orderby
drop table #tmp

---------------------------------------------------------------------------------------------
f001 orderby
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0000000001
1.1 00000000010000000001
1.1.1 000000000100000000010000000001
1.1.2 000000000100000000010000000002
1.1.3 000000000100000000010000000003
2 0000000002
2.3.10.10.11 00000000020000000003000000001000000000100000000011
2.3.10.10.12 00000000020000000003000000001000000000100000000012
2.3.10.10.13 00000000020000000003000000001000000000100000000013
3 0000000003
3.1 00000000030000000001
3.2 00000000030000000002
20.1 00000000200000000001

(所影响的行数为 13 行)
fcuandy 2008-03-01
  • 打赏
  • 举报
回复
睡觉了各位,早点休息.
-狙击手- 2008-03-01
  • 打赏
  • 举报
回复
熊来啦,快跑呀
dobear_0922 2008-03-01
  • 打赏
  • 举报
回复
fcuandy
人, 无完人;学, 无止境
等 级:
发表于:2008-03-01 00:07:0339楼 得分:0
嗯. 不错.

无论怎么做,目的都很简单,就是补0,呵呵.

事实证明,思路对了,方法是多样的.

----------------
位数对齐,然后排序,呵呵~!~!
-狙击手- 2008-03-01
  • 打赏
  • 举报
回复
看看
fcuandy 2008-03-01
  • 打赏
  • 举报
回复
嗯. 不错.

无论怎么做,目的都很简单,就是补0,呵呵.

事实证明,思路对了,方法是多样的.

Limpire 2008-03-01
  • 打赏
  • 举报
回复
--> 还是来个2005的吧,不用函数,无限级,各级字符长度最长可达10个字符,不过需要一个id:
declare @T table (id int identity, f001 varchar(20))
insert into @T
select '1' union all
select '2' union all
select '3' union all
select '1.1' union all
select '2.3.10.10.13' union all
select '2.3.10.10.12' union all
select '2.3.10.10.11' union all
select '3.1' union all
select '3.2' union all
select '20.1' union all
select '1.1.2' union all
select '1.1.1' union all
select '1.1.3'
;
with t1 as
(
select
a.id,data=replicate('0', 10-len(b.data))+b.data --> 每级长度10还不够,这里再增加前导0
from
(select id,data = convert(xml, '<root><v>' + replace(f001, '.', '</v><v>') + '</v></root>') from @T ) a
outer apply
(select data = N.v.value('.', 'varchar(100)') from a.data.nodes('/root/v') N(v)) b
)
select
* -- a.f001
from
@T a
outer apply
(select data = stuff((select '.' + data as [text()] from t1 where id = a.id for xml path('')), 1, 1, '')) b
order by
b.data
/*
id f001 data
----------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 0000000001
4 1.1 0000000001.0000000001
12 1.1.1 0000000001.0000000001.0000000001
11 1.1.2 0000000001.0000000001.0000000002
13 1.1.3 0000000001.0000000001.0000000003
2 2 0000000002
7 2.3.10.10.11 0000000002.0000000003.0000000010.0000000010.0000000011
6 2.3.10.10.12 0000000002.0000000003.0000000010.0000000010.0000000012
5 2.3.10.10.13 0000000002.0000000003.0000000010.0000000010.0000000013
3 3 0000000003
8 3.1 0000000003.0000000001
9 3.2 0000000003.0000000002
10 20.1 0000000020.0000000001
*/
fcuandy 2008-03-01
  • 打赏
  • 举报
回复
继续简化:




CREATE TABLE tb (ID INT IDENTITY(1,1),Code VARCHAR(20))
GO
INSERT tb SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT '1.1'
UNION ALL SELECT '2.1'
UNION ALL SELECT '131.2.1.11.1.131111'
UNION ALL SELECT '2.2'
UNION ALL SELECT '121.2.1.1.1.131111'
UNION ALL SELECT '3.1'
UNION ALL SELECT '3.2'
UNION ALL SELECT '20.1'
UNION ALL SELECT '1.1.1'
UNION ALL SELECT '1.1.2'
UNION ALL SELECT '1.1.3'
UNION ALL SELECT '1.1.11.1'
UNION ALL SELECT '1.1.2.1'
UNION ALL SELECT '2.1.2.1'
GO



CREATE FUNCTION dbo.ReplaceByPosition
(
@s1 VARCHAR(8000)
)
RETURNS VARCHAR(8000)

AS
BEGIN

DECLARE @perPositionSTR VARCHAR(100),@i INT,@tmpSTR VARCHAR(8000)
SELECT @i=0,@tmpSTR = ''
WHILE CHARINDEX('.',@s1)>0
BEGIN
SELECT @perPositionSTR = LEFT(@s1,CHARINDEX('.',@s1)-1),@tmpSTR=@tmpSTR + RIGHT('0000000000' + @perPositionSTR,10),@s1 = STUFF(@s1,1,CHARINDEX('.',@s1),'')
END
SET @tmpSTR=@tmpSTR + RIGHT('0000000000' + @perPositionSTR,10)

RETURN @tmpSTR
END
GO

SELECT * FROM tb
ORDER BY dbo.ReplaceByPosition(Code)

GO
DROP FUNCTION ReplaceByPosition
GO


DROP TABLE tb
GO
cchaha 2008-03-01
  • 打赏
  • 举报
回复
MARK
flying_dream031 2008-03-01
  • 打赏
  • 举报
回复
关注
CHRL 2008-03-01
  • 打赏
  • 举报
回复
哈哈,太感谢各位了,可以结贴了
Keambala 2008-03-01
  • 打赏
  • 举报
回复
Limpire 真强人。
能用上apply还有with, 佩服
tianyan316 2008-03-01
  • 打赏
  • 举报
回复
学习

Jeffrey84 2008-03-01
  • 打赏
  • 举报
回复
数据库用来存储和查询数据就好了,搞这么复杂干什么
fcuandy 2008-03-01
  • 打赏
  • 举报
回复
楼上朋友的不错.

while和用函数的道理一样. 因为靠简单的些字串处理函数不借助循环没有办法进行一一补位替换.

循环是函数的缩水版,体现不同.

个人看法.
加载更多回复(37)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧