SQL:按列排序，然后按子串混合 asc 和 desc [英] SQL: Order by column, then by substring mix asc and desc

问题描述

示例:在我的仓库中，我有几排，每排有 5 个货架.

Example: In my warehouse I have rows and each row has 5 shelves.

我走过过道，右边有第 1 行，左边有第 2 行，我开始从第 1 行从 1 到 5 选择一个订单，然后从 5 到 1 到第 2 行.

I'm walking through the aisle and have row 1 to my right and row 2 to my left, I start picking an order from row 1 going up from 1 to 5 then I go to row 2 down from 5 to 1.

我在数据库中有一个列如下:

I have a column in database as follows:

01-001-A-01
01-002-A-02
01-003-A-03
01-004-A-01
01-005-A-03
02-001-A-01
02-002-A-02
02-003-A-03
02-004-A-01
02-005-A-03

前 2 个字母是行，接下来的 3 个字母是货架，我想实现第二行应按 DESC 顺序排列，而第一个(和第三个等)应按 ASC 顺序排列.如下:

The first 2 letters are the rows and the next 3 letters are the shelves, I would like to achieve that the second row should be in DESC order while the first (and third etc) should be in ASC order. As follows:

01-001-A-01  
01-002-A-02  
01-003-A-03  
01-004-A-01  
01-005-A-03  
02-005-A-03  
02-004-A-01  
02-003-A-03  
02-002-A-02  
02-001-A-01 

所以基本上每个奇数行都是 ASC，每个偶数行都是 DESC.

So basically each odd row will be ASC and each even will be DESC.

推荐答案

Demo Here

select *
from #temp
order
by substring(id,1,2),
case
when substring(id,1,2)%2=0 then row_number() over (partition by substring(id,1,2) order by  SUBSTRING(id,4,3) desc)
else row_number() over (partition by substring(id,1,2) order by  SUBSTRING(id,4,3) asc)
end

这篇关于SQL:按列排序，然后按子串混合 asc 和 desc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！