一种选择是按性别划分人，为每个人获取rownum，以便稍后排序返回所需的输出。
使用示例数据：

SQL> select * from test;

        ID NAME         SAL GENDER
---------- ----- ---------- ----------
         1 AA          2000 M
         2 BB          1500 M
         3 CC          3000 F

质询：

SQL> with temp as(
  2    select t.*, rownum rn
  3    from test t
  4    where gender = 'M'
  5    union
  6    select t.*, rownum rn
  7    from test t
  8    where gender = 'F'
  9  )
 10  select row_number() over (order by rn) id,
 11    name, sal, gender
 12  from temp
 13  order by rn, gender;

        ID NAME         SAL GENDER
---------- ----- ---------- ----------
         1 AA          2000 M
         2 CC          3000 F
         3 BB          1500 M

SQL>

试试这个：

SELECT ID, A_NAME, SAL, GENDER
FROM    ( Select 
              ID, A_NAME, SAL, GENDER, ROWNUM "RN", ROW_NUMBER() OVER(Partition By GENDER Order By ID) "GENDER_RN" 
          From tbl
          Order By ID
        )
Order By GENDER_RN, RN

...使用您的示例数据：

WITH
    tbl AS
        ( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
          Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
          Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual 
        )

......结果是：

ID A_NAME        SAL GENDER
---------- ------ ---------- ------
         1 AA           2000 M      
         3 CC           3000 F      
         2 BB           1500 M

如果你有更多像这样的行：

WITH
    tbl AS
        ( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
          Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
          Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual Union All
          --
          Select 4 ID, 'DD' "A_NAME", 1800 "SAL", 'F' "GENDER" From Dual Union All
          Select 5 ID, 'EE' "A_NAME", 2100 "SAL", 'M' "GENDER" From Dual Union All
          Select 6 ID, 'FF' "A_NAME", 2700 "SAL", 'F' "GENDER" From Dual )

......结果是：

ID A_NAME        SAL GENDER
---------- ------ ---------- ------
         1 AA           2000 M      
         3 CC           3000 F      
         2 BB           1500 M      
         4 DD           1800 F      
         5 EE           2100 M      
         6 FF           2700 F

要将顺序从M F M F反转为F M F M，请在Order By子句中对RN使用DESC ...