这与mysql 5兼容：

select 
  MAX(case when id <= 2 then id END) AS id1,
  MAX(case when id > 2 then id END) AS id2
from (
  select 
  id,
  IF(id%2, 1, 0) as gp
  from TABLE1
) as s
GROUP BY gp
ORDER BY id1,id2

在没有看到更多数据的情况下，确切的要求并不完全清楚。下面是使用ROW_NUMBER()和透视逻辑的一种解释：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) - 1 AS rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN FLOOR(rn / 2) = 0 THEN id END) AS id1,
    MAX(CASE WHEN FLOOR(rn / 2) = 1 THEN id END) AS id2
FROM cte
GROUP BY rn % 2
ORDER BY 1;

这是一个可以正常工作的demo。

这个应该可以了。

select small.id as id1, big.id as id2
from(
  select id, title, 
    row_number() over (order by id) rn
  from demo
  where id < 3
) small
join (
  select id, title, 
    row_number() over (order by id) rn 
  from demo
  where id >=3
) big
on small.rn = big.rn

为了简单起见，我使用了JOIN，如果不能保证两个部分具有相同的行数，则可能必须使用LEFT、RIGHT或FULL OUTER JOIN
我放了一个working example on dbfiddle。
与使用GROUP BY的变体相比，这种方法可以很容易地在最终查询中使用所有其他列。

通常用于N行的表

WITH yourTable AS (
    SELECT 1 AS id, 'one' AS title UNION ALL
    SELECT 2, 'two' UNION ALL
    SELECT 3, 'three' UNION ALL
    SELECT 4, 'four' UNION ALL
    SELECT 5, 'five' UNION ALL
    SELECT 6, 'six' UNION ALL
    SELECT 7, 'seven'
),
cte AS (
    SELECT *, 
      ROW_NUMBER() OVER(ORDER BY id) - 1 AS rn,
      count(*) over() cnt
    FROM yourTable
)
SELECT 
    max(CASE WHEN rn < cnt / 2 THEN id END) AS id1,
    max(CASE WHEN rn >=cnt / 2 THEN id END) AS id2
FROM cte
GROUP BY rn % round(cnt/2) 
ORDER BY 1;

返回

id1 id2
1   5
2   6
3   7
4   null