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

来源：互联网发布：mac版spss使用教程编辑：程序博客网时间：2024/05/17 04:22

# mysql写法SELECT *, ABS(NOW() - startTime)  AS diffTimeFROM PolicyShuPriceORDER BY diffTime ASCLIMIT 0, 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);

阅读全文

0 0