mysql - 根据列值显示替代结果

Question

我有一个包含以下数据的表格

id      name            gender(Enum : Male,Female)
1       John Doe        Male
2       Panther Jow     Male
3       Faran Aktar     Female
4       Rohan Fusi      Male
5       Soham Das       Female
6       Rohit Dutta     Female
7       Sozi Petacus    Male
8       Tuhin Dtui      Male

我想要一个 SQL 查询，其中结果应该连续查看一男一女，然后再一男一女。如果在男性或女性结果之后，如果无法获得下一个女性或男性数据，那么所有结果都会显示。像这样

   id      name            gender(Enum : Male,Female)
    1       John Doe        Male
    3       Faran Aktar     Female
    2       Panther Jow     Male
    5       Soham Das       Female
    4       Rohan Fusi      Male
    6       Rohit Dutta     Female
    7       Sozi Petacus    Male 
    8       Tuhin Dtui      Male

Answer 1

你可以试试这个:

SELECT rnk, name ,gender
FROM (
  SELECT STRAIGHT_JOIN id, name, @y:=IF(@gender=gender,@y+1,1) AS rnk, @gender:=gender AS gender
  FROM (SELECT @gender:=NULL) AS x
  JOIN test
  ORDER BY gender, name DESC
) AS t
ORDER BY rnk ASC, gender DESC;

FIDDLE DEMO