数据库里有1,2,3,4,5 共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?
数据库里有1,2,3,4,5 共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?
CREATE TABLE #AAA (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
下面这句执行5次
insert #AAA values (newid())
![](/Images/OutliningIndicators/None.gif)
查看执行结果
select * from #AAA
![](/Images/OutliningIndicators/None.gif)
1、 第一种
select * from #AAA
order by case id when 4 then 1
when 5 then 2
when 1 then 3
when 2 then 4
when 3 then 5 end
![](/Images/OutliningIndicators/None.gif)
2、 第二种
select * from #AAA order by (id+2)%6
![](/Images/OutliningIndicators/None.gif)
3、 第三种
select * from #AAA order by charindex(cast(id as varchar),'45123')
![](/Images/OutliningIndicators/None.gif)
4、 第四种
select * from #AAA
WHERE id between 0 and 5
order by charindex(cast(id as varchar),'45123')
![](/Images/OutliningIndicators/None.gif)
5、 第五种
select * from #AAA order by case when id >3 then id-5 else id end
![](/Images/OutliningIndicators/None.gif)
6、 第六种
select * from #AAA order by id / 4 desc,id asc
![](/Images/OutliningIndicators/None.gif)