mysql 几种排序_MYSQL 语句多种排序

mysql 语句

SELECT *,IF(((UNIX_TIMESTAMP(NOW())-`END_TIME`) <0), 1, 0) AS t, UNIX_TIMESTAMP(NOW())-`end_time` AS now FROM `demo` ORDER BY top DESC, t DESC, end_time ASC

数据

--

-- 表的结构 `demo`

--

CREATE TABLE IF NOT EXISTS `demo` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`start_time` int(11) NOT NULL,

`end_time` int(11) NOT NULL,

`top` int(1) NOT NULL DEFAULT '0',

`name` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--

-- 转存表中的数据 `demo`

--

INSERT INTO `demo` (`id`, `start_time`, `end_time`, `top`, `name`) VALUES

(1, 1397463000, 1397513400, 0, '没有过期2014-04-14 08:10'),

(2, 1397556600, 1397679000, 0, '没有过期2014-04-15 10:10'),

(3, 1397211000, 1397247000, 0, '已过期2014-04-11 10:10'),

(4, 1397643000, 1397679000, 1, '置顶没有过期2014-04-16 10:10'),

(5, 1397729400, 1397851800, 0, '没有过期2014-04-17 10:10'),

(6, 1397902200, 1397938200, 0, '没有过期2014-04-19 10:10');

执行效果