假设现在有这样一个需求: 数据库中有一张产品订单表,表中有Date类型的字段CREATE_DATE代表订单创建的日期,要求获取创建日期为上个月25号到本月24号之间的订单.很明显,这个区间对应一个月的跨度,但是并不是自然月,这个需求该怎么实现呢?为了说明如何解决该需求,先创建表ProductOrder.
ProductOrder
使用以下脚本创建表并插入一些原始数据:
CREATE TABLE IF NOT EXISTS ProductOrder(
ID int(11) AUTO_INCREMENT COMMENT '订单表主键',
ORDER_ID bigint(64) NOT NULL COMMENT '订单表唯一业务ID',
PRODUCT_ID bigint(64) NOT NULL COMMENT '产品唯一编号ID',
CREATE_DATE date NOT NULL COMMENT '订单创建时间',
CREATE_TIMESTAMP timestamp NOT NULL default current_timestamp COMMENT '订单创建时间戳',
PRIMARY KEY(ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-24','2018-03-24 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-25','2018-03-25 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-26','2018-03-26 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-27','2018-03-27 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-21','2018-04-21 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-23','2018-04-23 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-24','2018-04-24 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-25','2018-04-25 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-26','2018-04-26 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-27','2018-04-27 23:33:33');
相关函数
DATE_SUB
DATE_SUB用于减去指定的日期,可以按日减,按月减,按年减等.它的语法是这样的DATE_SUB(date,INTERVAL expr type),date代表原始日期,比如2018-04-23.INTERVAL是固定值,expr代表减去的数目,type代表减去的时间单位,比如减去一个月的表达式就是DATE_SUB('2018-04-23',INTERVAL 1 MONTH),得到的结果是2018-03-23.更多可以使用的单位和细节参考MySQL DATE_SUB() 函数.另外,如果是加上一定的时间,可以使用DATE_ADD函数.
LEFT
mysql可以使用left(), right(), substring(), substring_index()等字符串截取函数截取字符串.这里以left为例,它的表达式是left(str,length).str是待截取的字符串,这里假设是2018-03-23,length代表从左边开始数几位开始截取,假设length为8,最终表达式为left('2018-03-23','8'),最终截取以后得到的字符串就是2018-03-.关于字符串截取参考MySQL字符串函数substring：字符串截取
CONCAT
mysql中可以使用CONCAT函数拼接多个字符串,比如要把刚刚截取的字符串和25拼接到一块的表达式就是CONCAT('2018-03-','25'),这样就得到了2018-03-25.关于CONCAT参考mysql函数之四：concat() mysql 多个字段拼接和SQL中字符串拼接.补充一点,oracle和db2中可以使用||拼接字符串,mysql中不行.
最终方案
方案概述
先使用CURRENT_DATE获取当前日期,然后使用DATE_SUB减去一个月,LEFT裁剪8位再拼接上25,这样得到了区间的最小日期.再使用CURRENT_DATE获取当前日期,LEFT裁剪8位再拼接上24,这样得到了区间的最大日期.
最终的sql
根据上面的思路得到的sql如下:
SELECT * from ProductOrder WHERE CREATE_DATE>=CONCAT(LEFT(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH),'8'),'25')
AND CREATE_DATE<=CONCAT(LEFT(CURRENT_DATE,'8'),'24')
执行结果
执行上面的sql,结果如下:
ID ORDER_ID PRODUCT_ID CREATE_DATE CREATE_TIMESTAMP
2 1 1 2018-03-25 2018-03-25 23:33:33
3 1 1 2018-03-26 2018-03-26 23:33:33
4 1 1 2018-03-27 2018-03-27 23:33:33
5 1 1 2018-04-21 2018-04-21 23:33:33
6 1 1 2018-04-23 2018-04-23 23:33:33
7 1 1 2018-04-24 2018-04-24 23:33:33
本人执行sql的时间是4月23号晚上,对应的日期区间是3月25号到4月24号.可以看到3月25号之前和4月24号之后的数据都被过滤掉了.
网友评论