Declare @OrderBy smallint Declare @t table(a1 int,a2 int,a3 int)select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end
if @orderby = 1 select * from @t order by a1 else if @orderby = 2 select * from @t order by a2 else if @orderby = 3 select * from @t order by a3
--下面是测试 Declare @OrderBy smallint Declare @t table(a1 int,a2 int,a3 int) insert into @t select 1,2,3 union all select 2,3,4 union all select 3,3,3 union all select 6,4,3 union all select 2,5,7--测试1 set @OrderBy=1 select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end--测试2 set @OrderBy=2 select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end--测试3 set @OrderBy=3 select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end/*--测试结果--测试1 a1 a2 a3 ----------- ----------- ----------- 1 2 3 2 3 4 2 5 7 3 3 3 6 4 3(所影响的行数为 5 行)--测试2 a1 a2 a3 ----------- ----------- ----------- 1 2 3 2 3 4 3 3 3 6 4 3 2 5 7(所影响的行数为 5 行)--测试3 a1 a2 a3 ----------- ----------- ----------- 1 2 3 3 3 3 6 4 3 2 3 4 2 5 7(所影响的行数为 5 行) --*/
create table t(a1 int,a2 int,a3 int) insert t values(1,2,3) insert t values(3,3,2) insert t values(2,1,1) goDeclare @OrderBy smallint declare @sql varchar(1000) set @OrderBy = 1 set @sql = 'select * from t order by case '+ convert(varchar,@OrderBy) + ' when 1 then a1 when 2 then a2 when 3 then a3 end' exec(@sql)
1.如果是a1,a2,a3都是varchar列呢? 处理方法一样.--下面是测试 Declare @OrderBy smallint Declare @t table(a1 varchar(1),a2 varchar(1),a3 varchar(1)) insert into @t select 'a','b','c' union all select '2','a','4' union all select '3','3','3' union all select '6','b','a'--测试1 set @OrderBy=1 select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end--测试2 set @OrderBy=2 select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end--测试3 set @OrderBy=3 select * from @t order by case @OrderBy when 1 then a1 when 2 then a2 when 3 then a3 end/*--测试结果--测试1 a1 a2 a3 ---- ---- ---- 2 a 4 3 3 3 6 b a a b c(所影响的行数为 4 行)--测试2 a1 a2 a3 ---- ---- ---- 3 3 3 2 a 4 a b c 6 b a(所影响的行数为 4 行)--测试3 a1 a2 a3 ---- ---- ---- 3 3 3 2 a 4 6 b a a b c(所影响的行数为 4 行)--*/
Declare @t table(a1 int,a2 int,a3 int)select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end
select * from @t order by a1
else if @orderby = 2
select * from @t order by a2
else if @orderby = 3
select * from @t order by a3
Declare @OrderBy smallint
Declare @t table(a1 int,a2 int,a3 int)
insert into @t
select 1,2,3
union all select 2,3,4
union all select 3,3,3
union all select 6,4,3
union all select 2,5,7--测试1
set @OrderBy=1
select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end--测试2
set @OrderBy=2
select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end--测试3
set @OrderBy=3
select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end/*--测试结果--测试1
a1 a2 a3
----------- ----------- -----------
1 2 3
2 3 4
2 5 7
3 3 3
6 4 3(所影响的行数为 5 行)--测试2
a1 a2 a3
----------- ----------- -----------
1 2 3
2 3 4
3 3 3
6 4 3
2 5 7(所影响的行数为 5 行)--测试3
a1 a2 a3
----------- ----------- -----------
1 2 3
3 3 3
6 4 3
2 3 4
2 5 7(所影响的行数为 5 行)
--*/
insert t values(1,2,3)
insert t values(3,3,2)
insert t values(2,1,1)
goDeclare @OrderBy smallint
declare @sql varchar(1000)
set @OrderBy = 1
set @sql = 'select * from t order by case '+ convert(varchar,@OrderBy) + ' when 1 then a1 when 2 then a2 when 3 then a3 end'
exec(@sql)
如果其中一个我需要DESC,两个ASC呢?谢谢.解决就结帐!!!BTW,还是希望不用exec(@sql)解决
-------
难!
处理方法一样.--下面是测试
Declare @OrderBy smallint
Declare @t table(a1 varchar(1),a2 varchar(1),a3 varchar(1))
insert into @t
select 'a','b','c'
union all select '2','a','4'
union all select '3','3','3'
union all select '6','b','a'--测试1
set @OrderBy=1
select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end--测试2
set @OrderBy=2
select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end--测试3
set @OrderBy=3
select * from @t
order by case @OrderBy
when 1 then a1
when 2 then a2
when 3 then a3 end/*--测试结果--测试1
a1 a2 a3
---- ---- ----
2 a 4
3 3 3
6 b a
a b c(所影响的行数为 4 行)--测试2
a1 a2 a3
---- ---- ----
3 3 3
2 a 4
a b c
6 b a(所影响的行数为 4 行)--测试3
a1 a2 a3
---- ---- ----
3 3 3
2 a 4
6 b a
a b c(所影响的行数为 4 行)--*/