table1:id minL maxL dd
------------------------
1 1 2 'foo'
2 2 4 'bar'
3 2 3 'foo2'
4 2 1 'foo3'
5 1 4 'bar1'
6 1 3 'foo4'
7 1 4 'bar2'
8 3 4 'bar3'
......假定id ,minL,maxL均为数字类型,dd为字符类型。
结果:查出所有minL=1或者maxL=4的行,要求minL=1并且maxL=4的行显示在最上面,最终结果集按id降序,即下面的结果id minL maxL dd
-----------------------
7 1 4 'bar2'
5 1 4 'bar1'
8 3 4 'bar3'
6 1 3 'foo4'
2 2 4 'bar'
------------------------
1 1 2 'foo'
2 2 4 'bar'
3 2 3 'foo2'
4 2 1 'foo3'
5 1 4 'bar1'
6 1 3 'foo4'
7 1 4 'bar2'
8 3 4 'bar3'
......假定id ,minL,maxL均为数字类型,dd为字符类型。
结果:查出所有minL=1或者maxL=4的行,要求minL=1并且maxL=4的行显示在最上面,最终结果集按id降序,即下面的结果id minL maxL dd
-----------------------
7 1 4 'bar2'
5 1 4 'bar1'
8 3 4 'bar3'
6 1 3 'foo4'
2 2 4 'bar'
from tb
where minL=1 or maxL=4
order by
case when minL=1 and maxL=4 then 1 else 2 end,
id desc
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[minL] int,[maxL] int,[dd] varchar(4))
insert [tb]
select 1,1,2,'foo' union all
select 2,2,4,'bar' union all
select 3,2,3,'foo2' union all
select 4,2,1,'foo3' union all
select 5,1,4,'bar1' union all
select 6,1,3,'foo4' union all
select 7,1,4,'bar2' union all
select 8,3,4,'bar3'
---查询---
select *
from tb
where minL=1 or maxL=4
order by
case when minL=1 and maxL=4 then 1 else 2 end,
id desc---结果---
id minL maxL dd
----------- ----------- ----------- ----
7 1 4 bar2
5 1 4 bar1
8 3 4 bar3
6 1 3 foo4
2 2 4 bar
1 1 2 foo(6 行受影响)