22,199
社区成员
发帖
与我相关
我的任务
分享
USE master;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO
CREATE 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
*/
create table tb(i_port int)
insert tb select 2
insert tb select 0
insert tb select 1001
insert tb select 200
insert tb select 101
insert tb select 34
select 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 行受影响)
*/