大家好,我有个排序问题请教大家!要求是这样的:
declare @b table (a2 int,b2 int,c2 int)
insert into @b select 7,9,1
insert into @b select 5,1,1
insert into @b select 8,9,1
insert into @b select 3,5,2
insert into @b select 5,5,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,1,1
insert into @b select 3,5,2
insert into @b select 5,9,2
首先是按照c2进行排序(升序),在按照b2字段的值 按9,1,5 进行排序(b2只有这三种取值)
declare @b table (a2 int,b2 int,c2 int)
insert into @b select 7,9,1
insert into @b select 5,1,1
insert into @b select 8,9,1
insert into @b select 3,5,2
insert into @b select 5,5,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,1,1
insert into @b select 3,5,2
insert into @b select 5,9,2
首先是按照c2进行排序(升序),在按照b2字段的值 按9,1,5 进行排序(b2只有这三种取值)
insert into @b select 7,9,1
insert into @b select 5,1,1
insert into @b select 8,9,1
insert into @b select 3,5,2
insert into @b select 5,5,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,1,1
insert into @b select 3,5,2
insert into @b select 5,9,2 select * from @b
order by c2,
case b2 when 9 then 1
when 1 then 2
when 5 then 3
else 4
end/*
a2 b2 c2
----------- ----------- -----------
7 9 1
8 9 1
5 1 1
7 1 1
4 5 1
4 5 1
5 9 2
3 5 2
3 5 2
5 5 2(10 row(s) affected)
*/
insert into @b select 7,9,1
insert into @b select 5,1,1
insert into @b select 8,9,1
insert into @b select 3,5,2
insert into @b select 5,5,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,1,1
insert into @b select 3,5,2
insert into @b select 5,9,2
select * from @b order by c2,case b2 when 9 then 1 when 1 then 2 else 3 enda2 b2 c2
----------- ----------- -----------
7 9 1
8 9 1
5 1 1
7 1 1
4 5 1
4 5 1
5 9 2
3 5 2
3 5 2
5 5 2(10 行受影响)
select * from @b order by c2,case b2 when 9 then 0 when 1 then 1 when 5 then 2 end
create table b(a2 int,b2 int,c2 int)
insert into b select 7,9,1
insert into b select 5,1,1
insert into b select 8,9,1
insert into b select 3,5,2
insert into b select 5,5,2
insert into b select 4,5,1
insert into b select 4,5,1
insert into b select 7,1,1
insert into b select 3,5,2
insert into b select 5,9,2
gocreate function dbo.aa()
returns @t table(a2 int,b2 int,c2 int)
as
begin
declare @c table(id int identity(1,1),c2 int)
declare @n int,@m int,@v int
set @m=1
select @n=count(distinct c2) from b
if(@n<1) return
insert into @c select distinct c2 from b order by c2 asc
while(@m<=@n)
begin
select @v=c2 from @c where id=@m
if exists(select 1 from b where c2=@v)
declare @p table(a2 int,b2 int,c2 int)
insert into @p select * from b where c2=@v
if exists(select 1 from @p where b2=1 and c2=@v)
insert into @t select * from @p where b2=1 and c2=@v
if exists(select 1 from @p where b2=5 and c2=@v)
insert into @t select * from @p where b2=5 and c2=@v
if exists(select 1 from @p where b2=9 and c2=@v)
insert into @t select * from @p where b2=9 and c2=@v
set @m=@m+1
end
return
endselect * from dbo.aa()drop table b
drop function dbo.aa