Mysql特殊替代排序

Question

我想对下面的 mysql 数据库表记录进行特殊排序，我想根据颜色对它们进行排序，所以第一行是红色，第二行是绿色，第三行是红色，第四行是绿色，所以我想要它们排序为

red, green, red, green, red,..

用mysql命令怎么实现呢？

在用于测试目的的 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');

谢谢，

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