MySQL特殊排序的替代方法

Question

我希望对下面的MySQL数据库表记录进行特殊排序，我想根据颜色顺序排序，第一行是红色，第二行是绿色，第三行是红色，第四行是绿色，以此类推，所以我希望它们按如下方式排序：

红色、绿色、红色、绿色、红色等。

如何使用MySQL order实现这一点？

以下是用于测试目的的MySQL表：

CREATE TABLE `fav_color` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `color` enum('red','green') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'red',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

INSERT INTO `fav_color` (`id`, `name`, `color`) VALUES
    (1, 'test', 'red'),
    (2, 'testing 33', 'red'),
    (3, 'test 444', 'green'),
    (4, 'test 555', 'red'),
    (5, 'test 6666', 'green'),
    (6, 'test 7777', 'red'),
    (7, 'test 8888', 'red'),
    (8, 'test 9999', 'red'),
    (9, 'test 1000', 'green'),
    (10, 'test 11111', 'green'),
    (11, 'test 122222', 'green'),
    (12, 'test 13333333', 'green');

谢谢。

- billoo

Answer 1

您可以按颜色排序，获取行号并更改奇偶行的顺序：

select * from (
  select @rownum := @rownum + 1 AS rank, fav_color.* 
  from fav_color cross join (select @rownum := 0) t
  order by color, id
) t
order by 
  -- Here @rownum is equal to count of records of subquery
  (1 - rank % 2) * (@rownum + @rownum % 2 - rank + 1) +  -- even
  (rank % 2) * rank, -- odd
  color

- Nick

Answer 2

这有点棘手。一种方法是枚举每种颜色的值，然后按枚举汇总：

select c.*
from (select c.*,
             (@rn := if(@c = color, @rn + 1,
                        if(@c := color, 1, 1)
                       )
             ) as rn
      from fav_color c cross join
           (select @c := '', @rn := 0) params
      order by c.color, c.id
     ) c
order by rn, field(color, 'red', 'green');

- Gordon Linoff

Answer 3

您可以按颜色排序，获取行号并更改奇偶行的顺序：[感谢mnv]

set @rownum := 0;
select * from (select @rownum := @rownum + 1 AS sort_col_fld, fav_color.* 
    from fav_color order by color, id) as tmp_table_alias
order by 
    -- Here @rownum is equal to count of records of subquery
    (1 - sort_col_fld % 2) * (@rownum + @rownum % 2 - sort_col_fld + 1) +  -- Even
    (sort_col_fld % 2) * sort_col_fld   -- Odd
    , color

- billoo