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

Question

create table tb(f001 decimal(10,2))
insert tb select 1 
insert tb select 2 
insert tb select 3 
insert tb select 1.1 
insert tb select 2.1 
insert tb select 2.2 
insert tb select 3.1 
insert tb select 3.2 
insert tb select 20.1select f001 from tb order by f001drop table tb/*
f001
---------------------------------------
1.00
1.10
2.00
2.10
2.20
3.00
3.10
3.20
20.10(9 行受影响)
*/

Answer 1

可以结帖了.
结帖方式:管理帖子->结分->输入密码->结帖.
谢谢

Answer 2

create table tb(f001 varchar(10))
insert tb select '1' 
insert tb select '2' 
insert tb select '3'
insert tb select '1.1' 
insert tb select '2.1' 
insert tb select '2.2' 
insert tb select '3.1' 
insert tb select '3.2' 
insert tb select '20.1'select f001 from tb 
order by cast(f001 as decimal(10,1))1
1.1
2
2.1
2.2
3
3.1
3.2
20.1

Answer 3

select f001 from tb 
order by cast(f001 as decimal(10,1))
这样就是对的
佩服

Answer 4

to:liangCK  和 wzy_love_sly 两位大哥,不好意思,我还没有说清楚,
是这样子的,这样可以解决一级的问题,如果有这样的情况:
f001 
------                   
1 
2 
3 
1.1 
2.1 
2.2 
3.1 
3.2 
20.1
1.1.1
1.1.2
1.1.3 排序好后: 
f001 
------                   
1 
1.1
1.1.1
1.1.2
1.1.3 
2 
2.1 
2.2 
3 
3.1 
3.2 
20.1 

Answer 5

to:fcuandy 有没有写过这样的处理函数,没有多少规则的,就跟一本书的目录一样

Answer 6

create table tb(f001 varchar(10))
insert tb select '1'   
insert tb select '2'   
insert tb select '3'   
insert tb select '1.1'   
insert tb select '2.1'   
insert tb select '2.2'   
insert tb select '3.1'   
insert tb select '3.2'   
insert tb select '20.1' 
insert tb select '1.1.1' 
insert tb select '1.1.2' 
insert tb select '1.1.3'  select f001
from
(
   select *,f002=substring(f001,1,charindex('.',f001))
               +replace(substring(f001,charindex('.',f001)+1,10),'.','')
   from tb
) t
order by cast(f002 as decimal(10,5))drop table tb/*
f001
----------
1
1.1
1.1.1
1.1.2
1.1.3
2
2.1
2.2
3
3.1
3.2
20.1(12 行受影响)
*/

Answer 7

可以结帖了. 
结帖方式:管理帖子-> 结分-> 输入密码-> 结帖. 
谢谢

Answer 8

-- > 测试数据: @T
declare @T table (f001 varchar(5))
insert into @T
select '1' union all
select '2' union all
select '3' union all
select '1.1' union all
select '2.1' union all
select '2.2' 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'--> 如果只有1级超过1位，如20.1，而没有20.22.1的情况，来个简单点的：
select * from @T order by cast(left(f001,isnull(nullif(charindex('.',f001),0)-1,len(f001))) as int),f001

Answer 9

三级下你的结果是对的, 级一多就不能了.级数不限,只能用循环或函数完成.比如给你的代码加两条数据,create table tb(f001 varchar(10))
insert tb select '1'   
insert tb select '2'
insert tb select '1.2'   
insert tb select '3'
insert tb select '1.1.11.1'
insert tb select '1.1.2.1'
insert tb select '2.1.2.1'   
insert tb select '1.1'   
insert tb select '2.1'   
insert tb select '2.2'   
insert tb select '3.1'   
insert tb select '3.2'   
insert tb select '20.1' 
insert tb select '1.1.1' 
insert tb select '1.1.2' 
insert tb select '1.1.3'  select f001
from
(
   select *,f002=substring(f001,1,charindex('.',f001))
               +replace(substring(f001,charindex('.',f001)+1,10),'.','')
   from tb
) t
order by cast(f002 as decimal(10,5))drop table tb
/*
1
1.1
1.1.1
1.1.11.1
1.1.2
1.1.2.1
1.1.3
1.2
2
2.1
2.1.2.1
2.2
3
3.1
3.2
20.1
*/
结果很明显,有问题.
楼主,先不要给他分, 他想偷懒. 等他写好了再给.

Answer 10

    * liangCK
    * 小梁（努力，发奋，上进！）
    * 等 级：
发表于：2008-02-29 22:02:1813楼 得分:0
可以了.
100分请给我.    * liangCK
    * 小梁（努力，发奋，上进！）
    * 等 级：
发表于：2008-02-29 22:02:4214楼 得分:0
可以结帖了.  
结帖方式:管理帖子->   结分->   输入密码->   结帖.  
谢谢------------------------------显得你很肤浅，不用脑子用脚底板想都知道，这个排序肯定和各级的位数相关，你就把人家的点替换就解决问题了？

Answer 11

写个函数，最多可处理５级，，， 
create table tb(id int, ItemNO varchar(16))
insert tb select 1, '1.4.6.4' 
union all select 1, '1.4.6.5' 
union all select 1, '1.4.6.2' 
union all select 1, '1.4.6.13' 
union all select 1, '1.4.6.3.9' 
union all select 1, '1.4.6.3.10' 
union all select 1, '1.4.6.3.11' 
union all select 1, '10' 
union all select 1, '11' 
union all select 1, '2' 
union all select 1, '2.1' 
union all select 1, '2.1.1'go
create function fn_Hash(@ItemNO varchar(16))
returns bigint as
begin
    declare @result bigint, @multiple int
    select @result=0, @multiple=4 
    while charindex('.', @ItemNO)>0
    begin
        select @result=@result+left(@ItemNO, charindex('.', @ItemNO)-1)*power(100,@multiple)
            , @ItemNO = right(@ItemNO, len(@ItemNO)-charindex('.', @ItemNO))                        
            ,@multiple=@multiple-1                
    end    
    select @result=@result+@ItemNO*power(100,@multiple)
    return @result
end
goselect * from tb
order by dbo.fn_Hash(ItemNO)/*
id          ItemNO
----------- ----------------
1           1.4.6.2
1           1.4.6.3.9
1           1.4.6.3.10
1           1.4.6.3.11
1           1.4.6.4
1           1.4.6.5
1           1.4.6.13
1           2
1           2.1
1           2.1.1
1           10
1           11(12 row(s) affected)
*/drop function dbo.fn_Hash
drop table tb 

Answer 12

create function f_getcharcount(
@str varchar(8000),
@chr varchar(20)
) returns int
as
begin
declare @re int,@i int
select @re=0,@i=charindex(@chr,@str)+1
while @i>1
select @re=@re+1
,@str=substring(@str,@i,8000)
,@i=charindex(@chr,@str)+1
return(@re)
enddrop table tbcreate table tb(f varchar(50))
insert into tb select '1'
insert into tb select '1.1'
insert into tb select '1.1.1'
insert into tb select '1.1.2'
insert into tb select '1.1.3'
insert into tb select '2'
insert into tb select '2.1'
insert into tb select '2.2'
insert into tb select '3'
insert into tb select '3.1'
insert into tb select '3.2'
insert into tb select '20.1'
alter table tb add f2 varchar(50)update tb set f2=ltrim(dbo.f_getcharcount(f,'.'))update tb set tb.f2=f+tp.var 
from tb,(select 0 as f2,'.0.0.0' as var union select 1,'.0.0' union select 2,'.0')tp 
where tb.f2=tp.f2select f from tb
order by  
cast(PARSENAME(f2,4) as int),cast(PARSENAME(f2,3) as int),
cast(PARSENAME(f2,2) as int),cast(PARSENAME(f2,1) as int)1
1.1
1.1.1
1.1.2
1.1.3
2
2.1
2.2
3
3.1
3.2
20.1
麻烦点，但准确

Answer 13

我写个N级的吧, 不用算每个节点应该对应的顺序.算顺序有点麻烦.思路是用路径表示法,写起来简单一点,应该能满足你的要求了.
比如00001 00002 00003
00001 00004 00001
00001 00011 00001
00011 00001 00051只要楼主你每级的最大数不超过 999999999 就行.
也即,你的图书馆不超过 999999999 座, 每个图书馆的图书分类不超过 999999999类,每类不超过 999999999 小类,每小类书总数不超过 99999999 本,每本不超过 999999999 章,每一章不超过 99999999 个篇,每个节不超过 99999999 个节,每个节不超过 999999999 个...(无聊,废话多了一点,见谅)实在 999999999 还不够的话,将 @s变量加长,并改语句 RIGHT (.....,10)为相应的@s长度.当然也不是无级的,受varchar(8000)的限制.另外以下测试表中,id identity列不是必须的,仅仅为了写语句方便.
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 '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
--SELECT * FROM tbDECLARE @s VARCHAR(10)
SET @s='0000000000'
SELECT IDENTITY(INT,1,1) id INTO # FROM syscolumns,sysobjectsSELECT b.id id, a.id gid,RIGHT(@s+RIGHT(STUFF(Code+'.',b.id,LEN(Code),''),CHARINDEX('.',REVERSE(STUFF('.'+Code+'.',b.id,LEN(Code),'')))),10) perCode,Code
INTO base
FROM tb a
INNER JOIN # b
ON SUBSTRING(Code+'.',b.id,1)='.'
ORDER BY gid,id
GO
CREATE FUNCTION myJoinSTR
(
@cid INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @s VARCHAR(1000)
    SELECT @s=ISNULL(@s+'.','') + perCode FROM base WHERE gid = @cid
    RETURN @s
END
GOSELECT a.* FROM tb a
INNER JOIN
(SELECT DISTINCT gid,dbo.myJoinSTR(gid) Code FROM base) b
ON gid=id
ORDER BY b.Code
GO
DROP TABLE #,base
GO
DROP FUNCTION myJoinSTR
GO
DROP TABLE tb
GO
/*
1 1
4 1.1
11 1.1.1
12 1.1.2
15 1.1.2.1
13 1.1.3
14 1.1.11.1
2 2
5 2.1
16 2.1.2.1
6 2.2
3 3
8 3.1
9 3.2
10 20.1
7 121.2.1.1.1.131111
*/

Answer 14

-- > 测试数据: @T
declare @T table (f001 varchar(5))
insert into @T
select '1' union all
select '2' union all
select '3' union all
select '1.1' union all
select '2.1' union all
select '2.2' 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'--> 全数字，不超过4级的情况
select f001 from
(select *,c=f001+replicate('.0',3-len(f001)+len(replace(f001,'.',''))) from @T) t
order by
cast(parsename(c,4) as int),
cast(parsename(c,3) as int),
cast(parsename(c,2) as int),
cast(parsename(c,1) as int)

Answer 15

是指22楼的不能超过4级接着看看fcuandy写的N级的

Answer 16

嗯，思路差不多，用0填充。用了2005之后，很不想用函数去处理字符的问题，老想用XML来钻牛角尖，呵呵，不果也钻出来了

Answer 17

钻牛角尖失败，fcuandy的答案最完美

Answer 18

前面加些空格就可以实现了， 借用一下Axapta中的右对齐思想
select f001 from tb order by space(10-len(f001)) + rtrim(f001)----------------------------------------------

Answer 19

我这个呢declare @T table (f001 varchar(10))
insert into @T
select '1' union all
select '2' union all
select '3' union all
select '1.1' union all
select '2.1' union all
select '2.2' 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 '20.1.1' 
select *
from @t
order by charindex('.',f001+'.') asc,f001/*f001       
---------- 
1
1.1
1.1.1
1.1.2
1.1.3
2
2.1
2.2
3
3.1
3.2
20.1
20.1.1（所影响的行数为 13 行）
*/

Answer 20

一个变量用错地方,多调了近半小时...改进版:CREATE TABLE tb (ID INT IDENTITY(1,1) /*id 列可有可无*/,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'
GOCREATE FUNCTION dbo.ReplaceByPosition
(
@s1 VARCHAR(8000),
@s2 VARCHAR(8000),
@perPositionLen_s2 INT, --这个参数可有可无,有的话,下面函数体简单一点,无的话,也可以算出来.因为这里我定义为每一位长为10. 10个0
@spL VARCHAR(1) 
) 
RETURNS VARCHAR(8000)AS
BEGIN DECLARE @perPositionSTR VARCHAR(100),@i INT
SET @i=0
WHILE CHARINDEX(@spL,@s1)>0
BEGIN
SELECT @perPositionSTR = LEFT(@s1,CHARINDEX(@spL,@s1)-1)
SELECT @s1 = STUFF(@s1,1,CHARINDEX(@spL,@s1),''), @s2=STUFF(@s2,@i*@perPositionLen_s2+1,@perPositionLen_s2,RIGHT('0000000000' + @perPositionSTR,@perPositionLen_s2)),@i=@i+1
END
SET @s2=STUFF(@s2,@i*@perPositionLen_s2+1,@perPositionLen_s2,RIGHT('0000000000' + @s1,10))
RETURN @s2
END
GODECLARE @s VARCHAR(10),@sX VARCHAR(8000)
SET @s='0000000000'
DECLARE @m INT
SELECT @m=MAX(LEN(Code)-LEN(REPLACE(Code,'.',''))) FROM tb
SELECT @sX=REPLICATE(@s,@m+1)
SELECT * FROM tb
ORDER BY dbo.ReplaceByPosition(Code, LEFT(@sX,(LEN(Code)-LEN(REPLACE(Code,'.',''))+1)*10),10,'.')GO
DROP FUNCTION ReplaceByPosition
GO
DROP TABLE tb
GO
/*
1 1
4 1.1
12 1.1.1
13 1.1.2
16 1.1.2.1
14 1.1.3
15 1.1.11.1
2 2
5 2.1
17 2.1.2.1
7 2.2
3 3
9 3.1
10 3.2
11 20.1
8 121.2.1.1.1.131111
6 131.2.1.11.1.131111
*/

Answer 21

继续简化:
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'
GOCREATE 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
GOSELECT * FROM tb
ORDER BY dbo.ReplaceByPosition(Code)GO
DROP FUNCTION ReplaceByPosition
GO
DROP TABLE tb
GO

Answer 22

--> 还是来个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
*/

Answer 23

fcuandy 
人, 无完人;学, 无止境 
等 级：
 发表于：2008-03-01 00:07:0339楼 得分:0 
嗯.   不错.   无论怎么做,目的都很简单,就是补0,呵呵. 事实证明,思路对了,方法是多样的.  
 
----------------
位数对齐，然后排序，呵呵~！~！

Answer 24

Limpire 真强人。
能用上apply还有with， 佩服

Answer 25

虽然帖子结了，但这样热闹,我也来说说我的思路，其实用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)>0endselect * 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 行）

Answer 26

楼上朋友的不错.while和用函数的道理一样. 因为靠简单的些字串处理函数不借助循环没有办法进行一一补位替换.循环是函数的缩水版,体现不同. 个人看法.