MySQL之BETWEEN AND范围查询问题

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_31625935/article/details/89017212

关于MySQL的BETWEEN AND范围查询问题

 

一、时间范围

(当数据库字段中存储的是yyyy-MM-dd格式,即date类型;用between and查询参数yyyy-MM-dd格式时,包含头尾,相当于x>=y && x<=z.

当是yyyy-MM-dd HH:mm:ss格式,即datetime类型;用between and查询参数yyyy-MM-dd  HH:mm:ss格式时,包含头尾,x>=y && x<=z。参数yyyy-MM-dd格式时,只包含头,相当于x>=y && x<z。)

查询参数格式与数据库类型相对应时,between and包含头尾,否则依情况

示例:

  • datetime类型,参数带时分秒(yyyy-MM-dd HH:mm:ss
SELECT e.create_time data FROM sys_enforce_event e WHERE 
     e.create_time BETWEEN '2019-03-22 09:39:33' AND '2019-03-25 15:17:51' 

查询结果如下图:

结论:  若datetime类型时,查询参数带时分秒,BETWEEN AND相当于  x >= y && x<=z。即包含头尾。

 

  • datetime类型,参数不带时分秒(yyyy-MM-dd
SELECT e.create_time data FROM sys_enforce_event e WHERE 
	e.create_time BETWEEN '2019-03-22' AND '2019-03-25' 

查询结果如下图:

结论:  若datetime类型时,查询参数不带时分秒,BETWEEN AND相当于  x >= y && x<z。即只包含头。

 

  • date类型,参数不带时分秒(yyyy-MM-dd
SELECT l.create_time FROM sys_enforce_black_list l 
	WHERE l.create_time BETWEEN '2019-02-01' AND '2019-03-02'

查询结果如下图:

结论:  若date类型时,查询参数不带时分秒,BETWEEN AND相当于  x >= y && x<=z

即包含头尾。

 

二、数字范围

包含头尾,相当于:x>=y && x<=z

SELECT l.release_state FROM sys_enforce_black_list l 
	WHERE l.release_state BETWEEN 1 AND 4

如图所示:

 

 

 

 

 

展开阅读全文

基于mysql的日期范围查询

08-13

[code=SQL]rnCREATE TABLE `kgl_jfkxfdz` (rn `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',rn `JYRQ` date DEFAULT NULL COMMENT '交易日期',rn `JYSJ` time DEFAULT NULL COMMENT '交易时间',rn `SHMC` varchar(32) DEFAULT NULL COMMENT '商户名称',rn `KH` varchar(20) DEFAULT NULL COMMENT '卡号',rn `JYLX` varchar(8) DEFAULT NULL COMMENT '交易类型',rn `JYJE` decimal(12,2) DEFAULT NULL COMMENT '交易金额',rn PRIMARY KEY (`ID`)rn) ENGINE=InnoDB AUTO_INCREMENT=192 DEFAULT CHARSET=utf8;rnrnINSERT INTO `kgl_jfkxfdz` VALUES ('1', '2010-01-01', '06:36:18', '144440199000001','87601144000001100','消费','0.01');rnINSERT INTO `kgl_jfkxfdz` VALUES ('2', '2010-02-22', '06:41:36', '144440199000001','87601144000001100','撤销','-0.01');rnINSERT INTO `kgl_jfkxfdz` VALUES ('3', '2010-03-22', '06:48:34', '144440199000001','87601144000001100','消费','0.02');rnINSERT INTO `kgl_jfkxfdz` VALUES ('4', '2010-04-22', '06:57:28', '144440199000001','87601144000001100','撤销','-0.01');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-05-22', '07:02:20', '144440199000001','87601144000001100','消费','0.03');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-06-22', '07:02:20', '144440199000001','87601144000001100','消费','0.04');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-07-22', '07:02:20', '144440199000001','87601144000001100','消费','0.05');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-08-22', '07:02:20', '144440199000001','87601144000001100','消费','0.06');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-09-22', '07:02:20', '144440199000001','87601144000001100','消费','0.07');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-10-22', '07:02:20', '144440199000001','87601144000001100','消费','0.08');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-11-22', '07:02:20', '144440199000001','87601144000001100','消费','0.08');rnINSERT INTO `kgl_jfkxfdz` VALUES ('5', '2010-12-22', '07:02:20', '144440199000001','87601144000001100','消费','0.10');[/code]rnrn页面有两个时间下拉框:DateFrom、DateTo 分别代表两个时间参数的值,可以传到SQL语句中使用rn若两个参数都没有值,默认时间为当前年,当前月,当前日,请写出SQL语句实现下列业务需求:rnrn1.在这两个时间范围内统计出每一周的总交易额rn2.在这两个时间范围内统计出每一个月的总交易额rn3.在这两个时间范围内统计现每一季度的总交易额rn[code=SQL]rn第一问期望结果:rn交易总额 交易日期rnxx.xx xx年 总第xx周 当月xx周rnxx.xx xx年 总第xx周 当月xx周rnrn第二问期望结果:rn交易总额 交易日期rnxx.xx       xx年 xx月rnxx.xx xx年 xx月rnrn第三问期望结果:rn交易总额 交易日期rnxx.xx 第xx季度rnxx.xx       第xx季度rn[/code] 论坛

没有更多推荐了,返回首页