我知道的SQL

 在ORDER BY中使用表达式

--drop table t_orderby

create table t_orderby

(

c1 int null,

c2 varchar(10) null,

c3 varchar(10) null

)  

 

insert into t_orderby

select 1,'2','a1' union all

select 1,'1','a2' union all

select 3,'1','ab' union all

select 1,'4','b1'

 

1. c2列的数据按'4','1','2'的指定顺序排序

 

(1) 使用union

select * from t_orderby

where c2='4'

union all

select * from t_orderby

where c2='1'

union all

select * from t_orderby

where c2='2'

 

(2) 使用表达式方法1

select * from t_orderby

order by charindex(c2,'4,1,2')

(3) 使用表达式方法2，再加个按照c1倒序

select * from t_orderby

order by case

when c2='4' then 1

when c2='1' then 2

when c2='2' then 3

end,c1 desc

 

2. 随机排序

(1) 要求c2='4'排第一行，其他的行随机排序

select * from t_orderby

order by case

when c2='4' then 1

else 1+rand()

end

(2) 所有行随机排序

 

select * from t_orderby

order by newid()

(3) 随机取出第一行

 

select top 1 * from t_orderby

order by newid()