create table #t(a varchar(20))insert into #t values('A1') insert into #t values('A11') insert into #t values('A2') insert into #t values('A2.1') insert into #t values('A2.11') insert into #t values('A2.2')select * from #t order by left(a,1),convert(float,right(a,len(a)-1))select * from #t order by right(a+'00000000',8)
create table #t(a varchar(20))insert into #t values('A1') insert into #t values('A11') insert into #t values('A2') insert into #t values('A2.1') insert into #t values('A2.11') insert into #t values('A2.2') select * from #t order by right(a+'00000000',8)
今天,当你需要对一个没有排序ID的表进行排序,并且想给每一行数据都给一个序号。这相当于很多人问到的,如何在一个视图中为每一行增加一个自动增长的“ID”号。你可以这样写:Select a.Badge,Count(a.Badge) as xorder From cPayroll a , cPayroll b Where a.Badge > = b.Badge Group by a.Badge 看了以后的语句,你可能会觉得非常简单。在以上的基础上,还可以满足以下这样一个很实用的功能。一个表中的前20行分成第1组,从21行-->40行分成第2组,41-->60 分成第3组。也即是在一个表的基础上拓展用视图生成以下的数据。(xOrder 与 Group 都是视图生成的。)Badge xOrder Group A1 1 G1A2 2 G1. B1 21 G2B2 22 G2...C1 41 G3... 在上面的视图得出来以后, 我们就可以用 Select * from ViewName Where Group = 'G1' ('G2'....) 的语句进行分组或分页显示查询了。以下我们来生成这样的结果. 在排序功能实现了以后,再实现分组,就是略微变化一下而已:Select Badge , Count(a.Badge) as xorder , Cast(((Count(a.Badge)-1)/20 ) +1 as varchar(10)) as Group From cPayroll a , cPayroll b Where a.Badge > = b.Badge Group by a.Badge
--抄 select * from 表 order by cast(stuff(字段,1,1,'') as float)
create table #t(a varchar(20))insert into #t values('A1') insert into #t values('A11') insert into #t values('A2') insert into #t values('A2.1') insert into #t values('A2.11') insert into #t values('A2.2')select * from #t order by cast(right(a,len(a)-1) as float)
create table #t(a varchar(20))insert into #t values('A1') insert into #t values('A11') insert into #t values('A2') insert into #t values('A2.1') insert into #t values('A2.11') insert into #t values('A2.2') select * from #t order by right(a+'00000000',8)
create table #t(a varchar(20))insert into #t values('A1') insert into #t values('A11') insert into #t values('A2') insert into #t values('A2.1') insert into #t values('A2.11') insert into #t values('A2.2')select * from #t order by left(a,1),floor(convert(float,right(a,len(a)-1))), convert(float,isnull(right(a,len(a)-(case charindex('.',a) when 0 then len(a) else charindex('.',a) end)),'0'))
create table #t(a varchar(20))insert into #t values('A1') insert into #t values('A11') insert into #t values('A2') insert into #t values('A2.1') insert into #t values('A2.11') insert into #t values('A2.2') insert into #t values('A2.2.11') insert into #t values('A2.2.1') insert into #t values('A2.2.2')select *from #t order by convert(int,replace (reverse(parsename(reverse(a),1)),'A','')), convert(int,reverse(parsename(reverse(a),2))), convert(int,reverse(parsename(reverse(a),3))), convert(int,reverse(parsename(reverse(a),4))) drop table #t/*A1 A2 A2.1 A2.2 A2.2.1 A2.2.2 A2.2.11 A2.11 A11 */
insert into #t values('A11')
insert into #t values('A2')
insert into #t values('A2.1')
insert into #t values('A2.11')
insert into #t values('A2.2')select * from #t
order by left(a,1),convert(float,right(a,len(a)-1))select * from #t
order by right(a+'00000000',8)
insert into #t values('A11')
insert into #t values('A2')
insert into #t values('A2.1')
insert into #t values('A2.11')
insert into #t values('A2.2')
select * from #t
order by right(a+'00000000',8)
看了以后的语句,你可能会觉得非常简单。在以上的基础上,还可以满足以下这样一个很实用的功能。一个表中的前20行分成第1组,从21行-->40行分成第2组,41-->60 分成第3组。也即是在一个表的基础上拓展用视图生成以下的数据。(xOrder 与 Group 都是视图生成的。)Badge xOrder Group A1 1 G1A2 2 G1. B1 21 G2B2 22 G2...C1 41 G3... 在上面的视图得出来以后, 我们就可以用 Select * from ViewName Where Group = 'G1' ('G2'....) 的语句进行分组或分页显示查询了。以下我们来生成这样的结果. 在排序功能实现了以后,再实现分组,就是略微变化一下而已:Select Badge , Count(a.Badge) as xorder , Cast(((Count(a.Badge)-1)/20 ) +1 as varchar(10)) as Group From cPayroll a , cPayroll b Where a.Badge > = b.Badge Group by a.Badge
select * from 表
order by cast(stuff(字段,1,1,'') as float)
insert into #t values('A11')
insert into #t values('A2')
insert into #t values('A2.1')
insert into #t values('A2.11')
insert into #t values('A2.2')select * from #t order by cast(right(a,len(a)-1) as float)
我希望是A2.11排在A2.2之后。不是普通的 转化为float就行了。
A2.2
A2.11
insert into #t values('A11')
insert into #t values('A2')
insert into #t values('A2.1')
insert into #t values('A2.11')
insert into #t values('A2.2')
select * from #t
order by right(a+'00000000',8)
insert into #t values('A11')
insert into #t values('A2')
insert into #t values('A2.1')
insert into #t values('A2.11')
insert into #t values('A2.2')select *
from #t
order by left(a,1),floor(convert(float,right(a,len(a)-1))),
convert(float,isnull(right(a,len(a)-(case charindex('.',a) when 0 then len(a) else charindex('.',a) end)),'0'))
再增加点难度,看看能否,增加A2.2.1,A2.2.2,A2.2.11,
希望结果如下:A1
A2
A2.1A2.2
A2.2.1
A2.2.2
A2.2.11A2.11
A11
create table #t(a varchar(20))insert into #t values('A1')
insert into #t values('A11')
insert into #t values('A2')
insert into #t values('A2.1')
insert into #t values('A2.11')
insert into #t values('A2.2')
insert into #t values('A2.2.11')
insert into #t values('A2.2.1')
insert into #t values('A2.2.2')select *from #t
order by convert(int,replace (reverse(parsename(reverse(a),1)),'A','')),
convert(int,reverse(parsename(reverse(a),2))),
convert(int,reverse(parsename(reverse(a),3))),
convert(int,reverse(parsename(reverse(a),4)))
drop table #t/*A1
A2
A2.1
A2.2
A2.2.1
A2.2.2
A2.2.11
A2.11
A11
*/
A2
A2.1A2.2
A2.2.1
B2.2.2
BV2.2.11S2.11
S11