--处理示例--测试数据 create table tb1(id int,col1 varchar(10),col2 int) insert tb1 select 1,'aa',111 union all select 2,'aa',111 union all select 3,'aa',111 union all select 4,'bb',222 union all select 5,'bb',222 union all select 6,'cc',333 union all select 7,'cc',333 union all select 8,'cc',333 union all select 9,'cc',333 create table tb2(col1 varchar(10),size int) insert tb2 select 'aa',2 union all select 'cc',2 --改改顺序 union all select 'bb',1 go--排序函数 create function f_sort(@id int,@col1 varchar(10)) returns varchar(20) as begin declare @i int,@size int,@j int set @i=0 select @i=case when @size is null then @i+1 else @i end ,@size=case when @col1=col1 then size else @size end from tb2 select @j=count(*) from tb1 where id<@id and col1=@col1 return(right(10000000000+isnull(@j,0)/@size,10)+right(10000000000+@i,10)) end go--调用实现查询 select * from tb1 a order by dbo.f_sort(id,col1) go--删除测试 drop table tb1,tb2 drop function f_sort/*--结果 id col1 col2 ----------- ---------- ----------- 1 aa 111 2 aa 111 6 cc 333 7 cc 333 4 bb 222 3 aa 111 8 cc 333 9 cc 333 5 bb 222(所影响的行数为 9 行) --*/
--处理示例--测试数据 create table tb1(id int,col1 varchar(10),col2 int) insert tb1 select 1,'aa',111 union all select 2,'aa',111 union all select 3,'aa',111 union all select 4,'bb',222 union all select 5,'bb',222 union all select 6,'cc',333 union all select 7,'cc',333 union all select 8,'cc',333 union all select 9,'cc',333 create table tb2(col1 varchar(10),size int) insert tb2 select 'aa',2 union all select 'cc',2 --改改顺序 union all select 'bb',0 go--排序函数 create function f_sort(@id int,@col1 varchar(10)) returns varchar(20) as begin declare @i int,@size int,@j int set @i=0 select @i=case when @size is null then @i+1 else @i end ,@size=case when @col1=col1 then size else @size end from tb2 if @size=0 return(replicate(20,'9')) select @j=count(*) from tb1 where id<@id and col1=@col1 return(right(10000000000+isnull(@j,0)/@size,10)+right(10000000000+@i,10)) end go--调用实现查询 select * from tb1 a order by dbo.f_sort(id,col1) go--删除测试 drop table tb1,tb2 drop function f_sort/*--结果 id col1 col2 ----------- ---------- ----------- 1 aa 111 2 aa 111 6 cc 333 7 cc 333 3 aa 111 8 cc 333 9 cc 333 4 bb 222 5 bb 222(所影响的行数为 9 行) --*/
create table tb1(id int,col1 varchar(10),col2 int)
insert tb1 select 1,'aa',111
union all select 2,'aa',111
union all select 3,'aa',111
union all select 4,'bb',222
union all select 5,'bb',222
union all select 6,'cc',333
union all select 7,'cc',333
union all select 8,'cc',333
union all select 9,'cc',333 create table tb2(col1 varchar(10),size int)
insert tb2 select 'aa',2
union all select 'cc',2 --改改顺序
union all select 'bb',1
go--排序函数
create function f_sort(@id int,@col1 varchar(10))
returns varchar(20)
as
begin
declare @i int,@size int,@j int
set @i=0
select @i=case when @size is null then @i+1 else @i end
,@size=case when @col1=col1 then size else @size end
from tb2
select @j=count(*)
from tb1
where id<@id and col1=@col1
return(right(10000000000+isnull(@j,0)/@size,10)+right(10000000000+@i,10))
end
go--调用实现查询
select * from tb1 a
order by dbo.f_sort(id,col1)
go--删除测试
drop table tb1,tb2
drop function f_sort/*--结果
id col1 col2
----------- ---------- -----------
1 aa 111
2 aa 111
6 cc 333
7 cc 333
4 bb 222
3 aa 111
8 cc 333
9 cc 333
5 bb 222(所影响的行数为 9 行)
--*/
确实包含所有列值,但允许tb2的size为0。当size为0的时候就把他的全部记录排到最后.也就是说只对size>0的记录进行交替排序。
自我感觉好象很复杂。
create table tb1(id int,col1 varchar(10),col2 int)
insert tb1 select 1,'aa',111
union all select 2,'aa',111
union all select 3,'aa',111
union all select 4,'bb',222
union all select 5,'bb',222
union all select 6,'cc',333
union all select 7,'cc',333
union all select 8,'cc',333
union all select 9,'cc',333 create table tb2(col1 varchar(10),size int)
insert tb2 select 'aa',2
union all select 'cc',2 --改改顺序
union all select 'bb',0
go--排序函数
create function f_sort(@id int,@col1 varchar(10))
returns varchar(20)
as
begin
declare @i int,@size int,@j int
set @i=0
select @i=case when @size is null then @i+1 else @i end
,@size=case when @col1=col1 then size else @size end
from tb2
if @size=0 return(replicate(20,'9'))
select @j=count(*)
from tb1
where id<@id and col1=@col1
return(right(10000000000+isnull(@j,0)/@size,10)+right(10000000000+@i,10))
end
go--调用实现查询
select * from tb1 a
order by dbo.f_sort(id,col1)
go--删除测试
drop table tb1,tb2
drop function f_sort/*--结果
id col1 col2
----------- ---------- -----------
1 aa 111
2 aa 111
6 cc 333
7 cc 333
3 aa 111
8 cc 333
9 cc 333
4 bb 222
5 bb 222(所影响的行数为 9 行)
--*/