declare @a table(id int) insert into @a select 101 insert into @a select 102 insert into @a select 103 insert into @a select 104 insert into @a select 201 insert into @a select 202 insert into @a select 203 insert into @a select 204 insert into @a select 301 insert into @a select 302 insert into @a select 303 insert into @a select 304 insert into @a select 401 insert into @a select 402 insert into @a select 403 insert into @a select 404select id from @a order by case when right(id,1)=1 or right(id,1)=2 then 1 else 2 end,id-------------------------- id ----------- 101 102 201 202 301 302 401 402 103 104 203 204 303 304 403 404(16 row(s) affected)
declare @a table(id int) insert into @a select 101 insert into @a select 102 insert into @a select 103 insert into @a select 104 insert into @a select 201 insert into @a select 202 insert into @a select 203 insert into @a select 204 insert into @a select 301 insert into @a select 302 insert into @a select 303 insert into @a select 304 insert into @a select 401 insert into @a select 402 insert into @a select 403 insert into @a select 404 select * from @a order by case when id%100<=2 then 1 else 2 end
-->生成测试数据
declare @tb table([house] nvarchar(4)) Insert @tb select 101 union all select 102 union all select 103 union all select 104 union all select 201 union all select 202 union all select 203 union all select 204 union all select 301 union all select 302 union all select 303 union all select 304 union all select 401 union all select 402 union all select 403 Select [house] from @tb order by (cast( right([house],1) as int)-1)/2, (cast( left([house],len([house])-1) as int) ) /* house ----- 101 102 201 202 301 302 401 402 103 104 203 204 303 304 403 */
declare @pUnit int --每个单元的房间数目 set @pUnit =2 Select [house] from @tb order by (cast( right([house],1) as int)-1)/@pUnit, (cast( left([house],len([house])-1) as int) )
@pUnit int set @pUnit=2--每个单元的房间数目 Select [house] from tbl1 order by ([house]%100-1)/2,[house]/100--借用和尚的数据 declare @tb table([house] nvarchar(4)) Insert @tb select 101 union all select 102 union all select 103 union all select 104 union all select 201 union all select 202 union all select 203 union all select 204 union all select 301 union all select 302 union all select 303 union all select 304 union all select 401 union all select 402 union all select 403 union all select 404 Select [house] from @tb order by ([house]%100-1)/@pUnit,[house]/100
insert into @a select 101
insert into @a select 102
insert into @a select 103
insert into @a select 104
insert into @a select 201
insert into @a select 202
insert into @a select 203
insert into @a select 204
insert into @a select 301
insert into @a select 302
insert into @a select 303
insert into @a select 304
insert into @a select 401
insert into @a select 402
insert into @a select 403
insert into @a select 404select id from @a order by case when right(id,1)=1 or right(id,1)=2 then 1 else 2 end,id--------------------------
id
-----------
101
102
201
202
301
302
401
402
103
104
203
204
303
304
403
404(16 row(s) affected)
insert into @a select 101
insert into @a select 102
insert into @a select 103
insert into @a select 104
insert into @a select 201
insert into @a select 202
insert into @a select 203
insert into @a select 204
insert into @a select 301
insert into @a select 302
insert into @a select 303
insert into @a select 304
insert into @a select 401
insert into @a select 402
insert into @a select 403
insert into @a select 404 select * from @a order by case when id%100<=2 then 1 else 2 end
declare @tb table([house] nvarchar(4))
Insert @tb
select 101 union all
select 102 union all
select 103 union all
select 104 union all
select 201 union all
select 202 union all
select 203 union all
select 204 union all
select 301 union all
select 302 union all
select 303 union all
select 304 union all
select 401 union all
select 402 union all
select 403
Select [house] from @tb
order by (cast( right([house],1) as int)-1)/2,
(cast( left([house],len([house])-1) as int) )
/*
house
-----
101
102
201
202
301
302
401
402
103
104
203
204
303
304
403
*/
set @pUnit =2
Select [house] from @tb
order by (cast( right([house],1) as int)-1)/@pUnit,
(cast( left([house],len([house])-1) as int) )
set @pUnit=2--每个单元的房间数目
Select [house] from tbl1
order by ([house]%100-1)/2,[house]/100--借用和尚的数据
declare @tb table([house] nvarchar(4))
Insert @tb
select 101 union all
select 102 union all
select 103 union all
select 104 union all
select 201 union all
select 202 union all
select 203 union all
select 204 union all
select 301 union all
select 302 union all
select 303 union all
select 304 union all
select 401 union all
select 402 union all
select 403 union all
select 404
Select [house] from @tb
order by ([house]%100-1)/@pUnit,[house]/100
(16 行受影响)
house
-----
101
102
201
202
301
302
401
402
103
104
203
204
303
304
403
404
SELECT f1 FROM t ORDER BY (f1+1)/2,f1
不过其他的几个朋友我也要感谢。