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);
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值