mysql 取一条离当前时间最近的记录

mysql写法

SELECT *, ABS(NOW() - startTime)  AS diffTime
FROM PolicyShuPrice
ORDER BY diffTime ASC
LIMIT 0, 1

mysql优化改进版本

-- 当前时刻，向上找一条
SELECT * 
FROM policyshuprice
WHERE startTime > NOW()
LIMIT 1

-- 当前时刻，向下找一条
SELECT * 
FROM policyshuprice
WHERE startTime < NOW()
LIMIT 1

-- 优化的结果
EXPLAIN
SELECT * FROM 
(
	SELECT * FROM 
	(
		SELECT id, ABS(NOW() - startTime)  AS diffTime
		FROM policyshuprice
		WHERE startTime < NOW()
		LIMIT 1
	) a 
	UNION
	SELECT * FROM 
	(
		SELECT id, ABS(NOW() - startTime)  AS diffTime
		FROM policyshuprice
		WHERE startTime > NOW()
		LIMIT 1	
	) b
) t
ORDER BY diffTime ASC
LIMIT 1

oracle写法

SELECT * FROM (
	SELECT *, ABS(NOW() - startTime) AS diffTime
	FROM PolicyShuPrice
	ORDER BY diffTime ASC 
) C WHERE rownum=1

附带表结构

DROP TABLE IF EXISTS `policyshuprice`;

CREATE TABLE `policyshuprice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `policySku_id` int(11) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `price` decimal(10,4) DEFAULT NULL,
  `postFee` decimal(10,4) DEFAULT NULL,
  `remarks` varchar(200) DEFAULT NULL,
  `needConfirm` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `policyshuprice` */

insert  into `policyshuprice`(`id`,`policySku_id`,`startTime`,`price`,`postFee`,`remarks`,`needConfirm`) values (1,1,'2017-08-12 08:30:46','2.5000','1.0000','1',1),(2,2,'2017-08-12 08:50:46','3.5000','1.0000','1',1),(3,3,'2017-08-12 09:30:46','4.6000','1.0000','1',1);

/*Table structure for table `policysku` */

DROP TABLE IF EXISTS `policysku`;

CREATE TABLE `policysku` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `PolicyType` varchar(200) DEFAULT NULL,
  `Sku_id` int(11) DEFAULT NULL,
  `shop_id` int(11) DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `policysku` */

insert  into `policysku`(`id`,`PolicyType`,`Sku_id`,`shop_id`,`state`) values (1,'1',1,3,1),(2,'2',2,3,1),(3,'2',3,2,1);