SQL 實現交錯排序：奇偶行分別取前後半段遞增

需求說明

給定一張表 t0，包含三個整數欄位 column1、column2、column3。要求將資料按 column1 排序後，奇數行取前半段遞增，偶數行取後半段遞增，最終交錯輸出。

原始資料

預期結果

實現邏輯（T-SQL / SQL Server）

1. 先按 column1 排序，計算總筆數 @n。
2. 將前 @a = CEILING(@n/2) 筆放入游標 crs1（前半段，遞增）。
3. 將後 @b = @n - @a 筆放入游標 crs2（後半段，遞增）。
4. 迴圈交替從 crs1、crs2 取資料寫入暫存表 #t。
5. 最後輸出 #t。

核心程式碼

CREATE TABLE t0 (column1 INT, column2 INT, column3 INT);
INSERT INTO t0 VALUES
(11,12,13), (13,14,15), (21,22,23),
(22,23,24), (12,13,14), (25,26,27);

CREATE TABLE #t (column1 INT, column2 INT, column3 INT);

DECLARE @c1 INT, @c2 INT, @c3 INT, @n INT, @a INT, @b INT;
SELECT @n = COUNT(*) FROM t0;
IF (@n % 2 = 1) SET @a = @n/2 + 1 ELSE SET @a = @n/2;
SET @b = @n - @a;

-- 前半段游標（遞增）
EXEC('DECLARE crs1 CURSOR FOR SELECT TOP ' + @a + ' * FROM t0 ORDER BY column1');
-- 後半段游標（先降序取後 @b 筆，再遞增）
EXEC('DECLARE crs2 CURSOR FOR SELECT * FROM (SELECT TOP ' + @b + ' * FROM t0 ORDER BY column1 DESC) AS b ORDER BY column1 ASC');

OPEN crs1; OPEN crs2;

WHILE @n > 0
BEGIN
    IF @n % 2 = 0  -- 偶數行取前半段
    BEGIN
        FETCH NEXT FROM crs1 INTO @c1, @c2, @c3;
        INSERT INTO #t VALUES (@c1, @c2, @c3);
    END
    ELSE  -- 奇數行取後半段
    BEGIN
        FETCH NEXT FROM crs2 INTO @c1, @c2, @c3;
        INSERT INTO #t VALUES (@c1, @c2, @c3);
    END
    SET @n = @n - 1;
END

SELECT * FROM #t;
DEALLOCATE crs1; DEALLOCATE crs2;
DROP TABLE #t; DROP TABLE t0;

現代寫法建議（MySQL 8.0+ / PostgreSQL / SQL Server 2012+）

使用視窗函數 ROW_NUMBER() 與 NTILE(2) 可大幅簡化：

WITH sorted AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY column1) AS rn,
           NTILE(2) OVER (ORDER BY column1) AS grp
    FROM t0
),
front AS (SELECT * FROM sorted WHERE grp = 1 ORDER BY rn),
back  AS (SELECT * FROM sorted WHERE grp = 2 ORDER BY rn),
numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY rn) AS seq FROM front
    UNION ALL
    SELECT *, ROW_NUMBER() OVER (ORDER BY rn) AS seq FROM back
)
SELECT column1, column2, column3
FROM numbered
ORDER BY seq, CASE WHEN grp = 1 THEN 0 ELSE 1 END;

小結

• 游標寫法雖可行，但效能較差、程式碼冗長，僅適合舊版 SQL Server 環境。
• 現代資料庫建議使用 CTE + 視窗函數，邏輯清晰且效能優異。
• 此類「交錯合併」需求在報表、分頁顯示中較常見。