Mysql between的边界范围 between 的范围是包含两边的边界值 eg: id between 3 and 7 等价与 id >=3 and id<=7 not between 的范围是
between 的范围是包含两边的边界值
eg: id between 3 and 7 等价与 id >=3 and id<=7
not between 的范围是不包含边界值
eg:id not between 3 and 7 等价与 id < 3 or id>7
SELECT * FROM `test` where id BETWEEN 3 and 7;
等价于 SELECT * FROM `test` where id>=3 and id<=7;
-----------------------------------------------------------
SELECT * FROM `test` where id NOT BETWEEN 3 and 7;
等价于 SELECT * FROM `test` where id<3 or id>7;
mysql, between 开始日期 AND 结束日期 包含开始日期,不包含结束日期
BETWEEN '2018-01-22' AND '2018-01-30'
开始日期从2018-01-22 00:00:00.0 开始, 到2018-01-29 23:59:59.59结束
表中的CREATE_DATE 是varchar(21) DEFAULT NULL COMMENT '时间',
CREATE_DATE中保存值是: 年-月-日 时:分:秒:0 例如: 2018-01-29 23:45:35.0
SELECT * FROM Test a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-30'
ORDER BY a.CREATE_DATE desc
SELECT * FROM TABEL a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-30'
ORDER BY a.CREATE_DATE desc
2018-01-29 23:45:35.0 20180129
2018-01-29 23:45:33.0 20180129
2018-01-29 00:10:58.0 20180129
2018-01-29 00:10:45.0 20180129
2018-01-28 23:42:23.0 20180128
2018-01-28 23:39:39.0 20180128
SELECT * FROM TABEL a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-29'
ORDER BY a.CREATE_DATE desc
2018-01-28 23:42:23.0 20180128
2018-01-28 23:39:39.0 20180128
2018-01-28 00:13:22.0 20180128
2018-01-28 00:13:19.0 20180128
2018-01-27 23:23:02.0 20180127
2018-01-22 00:09:59.0 20180122
2018-01-22 00:09:56.0 20180122
2018-01-22 00:01:53.0 20180122
遇到的其他问题:
遇到另外一张表 test2 有保存时间的字段: `REPORTTIME` varchar(45) DEFAULT NULL,
这个字段保存的值是:
select * from bips_hpd_helpdesk a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d')
BETWEEN '2018-01-16' AND '2018-01-27' ORDER BY from_unixtime(a.REPORTTIME,'%Y-%m-%d') DESC ;
结果1:
从结果中,可以看到取到了27号的数据,可能是处理的时间没有 小时,分钟,秒。
select * from bips_hpd_helpdesk a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d')
BETWEEN str_to_date('2018-01-16','%Y-%m-%d') AND str_to_date('2018-01-27','%Y-%m-%d')
结果2:
找到问题: 毫秒值转换为时间,发现这里保存的毫秒值,没有保存时分秒:
from_unixtime(a.REPORTTIME,'%Y-%m-%d') AS reportTime,a.REPORTTIME,
str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s') AS reportTime22
FROM test a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s')
BETWEEN str_to_date('2018-01-16','%Y-%m-%d %h:%i:%s') AND str_to_date('2018-01-27 %h:%i:%s','%Y-%m-%d')
#subdate(curdate(),date_fORMat(curdate(),'%w')-1) AND subdate(curdate(),date_format(curdate(),'%w')-8)
ORDER BY from_unixtime(a.REPORTTIME,'%Y-%m-%d') DESC ;
查看到的时间值:
以上为个人经验,希望能给大家一个参考,也希望大家多多支持自学编程网。
--结束END--
本文标题: mysql中between的边界,范围说明
本文链接: https://www.lsjlt.com/news/10397.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
一口价域名售卖能注册吗?域名是网站的标识,简短且易于记忆,为在线用户提供了访问我们网站的简单路径。一口价是在域名交易中一种常见的模式,而这种通常是针对已经被注册的域名转售给其他人的一种方式。
一口价域名买卖的过程通常包括以下几个步骤:
1.寻找:买家需要在域名售卖平台上找到心仪的一口价域名。平台通常会为每个可售的域名提供详细的描述,包括价格、年龄、流
443px" 443px) https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294.jpg https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294-768x413.jpg 域名售卖 域名一口价售卖 游戏音频 赋值/切片 框架优势 评估指南 项目规模
0