数据库中有个整形字段,现在想对其按奇偶相间的方式进行排序,想请教一下有没有不通过创建临时表,而通过一个SQL语句实现的方法。谢谢。
举例:排序前:
i_port
2
0
1001
200
101
34排序后:
i_port
0
101
2
1001
34
200
举例:排序前:
i_port
2
0
1001
200
101
34排序后:
i_port
0
101
2
1001
34
200
insert tb select 2
insert tb select 0
insert tb select 1001
insert tb select 200
insert tb select 101
insert tb select 34select i_port from
(select *,id=row_number() over (partition by i_port%2
order by i_port) from tb) a
order by id,i_port/*
i_port
-----------
0
101
2
1001
34
200(6 行受影响)
*/
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
id int
)
INSERT INTO A--插入测试数据
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT 1001 UNION ALL
SELECT 200 UNION ALL
SELECT 101 UNION ALL
SELECT 34
go
WITH cte AS
(
select TOP 100 PERCENT id,
CASE WHEN id%2=0 THEN ROW_NUMBER()OVER(PARTITION BY CASE WHEN id%2=0 THEN 1 END ORDER BY id)*2-1
ELSE ROW_NUMBER()OVER(PARTITION BY CASE WHEN id%2<>0 THEN 1 END ORDER BY id)*2
END AS ou from A
)
SELECT id FROM cte ORDER BY ou
go
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO/*
id
-----------
0
101
2
1001
34
200*/