最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
sql按时间排序一些方法介绍
时间：2022-06-29 08:02:13 编辑：袖梨 来源：一聚教程网
在项目中遇到了这样的一个需求
“在用户参与的一个活动列表中，获取当前时间中刚刚开始了(也就是刚才开始的，不是最先开始)的一条活动；如果当前时间没有活动开始，则获取将要开始的一条活动记录”且必须在一次数据库查询操作中完成。
也就是必须要在数据库中做活动的状态区分。
现在具象化一点的理解这个需求，有这样一个表t_time
里面只有三个字段
id，starttime，endtime
我们假定现在每一条记录就是一个活动。
现在可以把需求抽象的分析为“当前时间大于starttime且小于endtime的按desc到序排列获取第一条记录，当前时间小于starttime且小于endtime的按asc正序排列获取第一条记录”且同样是要在一条sql语句中完成。
于是开始进行sql编写，在sql要完成两种状态的辨别，那就肯定是需要条件分支类似的语句控制。
mysql中的条件语句分支，常用的就是if ,case when 语句。
在找到if then 的用法后，开始第一次if then语句的测试
| 代码如下 | 复制代码 |
|
|
|
结果是；华丽丽的报错啊！！！
Err] 1064 – You have an error in your SQL syntax;
考虑到语法没有问题啊。mysql真是有待学习啊，在网上搜索后了解到“if then endif只能在procedure或是function里用”，手册也没看，就乱来。而且不能为了这么一个处理还单独去创建一个存储过程或函数。
再查看case when语句，这次是能在sql中直接执行的。
| 代码如下 | 复制代码 |
|
select * from t_time where endtime > UNIX_TIMESTAMP() |
|
结果
但是还是没有达到预期效果，case when 没有起到作用，加上case when语句别名后的结果
seo5AGzcY5JxOLCPMtG2LvZzjU9l9qssCyI8O5hBOljPM0K9J1GlR3n3BuJ0j1SKVLqqrXnOFjNZV+QbKSp50dWBXnUy3iaFeg7jUo52lkn+DQSktOw3L7Uc7qvJrLCviBp5EiC9DGeZgX6TqNSjtb4PMt2cqR5lo3DeOcaT7MCfadRlUY4RdZtx14zU8/v2akg+m0XgsTpOhhPswJ9p1Glgxx5B5+m2xAJ1xE3655rhWVBSmUfQ5A1XQfjaVag7zEqPfB9MQMEYXD5BbpmBfrIUWXHObcAa4AgDC6Np1mBPnJU2XHOLcAaIAiDS+NpVqCPHFV2nHMLsAYIwuDSeJoV6CNHlR3n3AKsAYIwuDQeVqADQCs6GK/aBf7PFWuAIAyDBeljPDwD3R0gCINL4+EZ6O4AQRhcGg/PQHcHCMLg0nimnoEetj6cpUu69SdC7GU2WOpHude2yAnGBanu1aufFcME0afz9Az07CRbzD/yu9RPqfHmNissC7LHXqV+VowRpCmdp2egL7OveCwuzBhNXAjKPc+aZ0JSvSBNAzFuvFLSPsaz8wz0xYDxSvc2pYjQT1PPcVDOtXgQZLluqqpoSuNZEESZzs0z0P9f7mzjleKl2SC3L/XceoJnvXkURG6T3da4+gSXxrPzDPQFxlPkWswLUuqk1F7YVkZcGs/UM9AnGu8cM8gzY1vP0413ftmaPoVttjFdkKZ0np6BPvgOPk3XK7IkB6xjz4ttQcJ1CAPpItEJwwTRp8Mz0A8ICMLg8gt0PAPdHSAIg0vj4Rno7gBBGFwaD89AdwcIwuDSeHgGujtAEAaXxsMKdABoRQfjVbvAFc8aIAjDYEH6GA8r0N0BgjC4NB5WoLsDBGFwaTysQHcHCMLg0nh2VqAP/kHQUvvV0rZfPyn3MvgLqaWTINW9evWzYpgg+nRuVqAzxeO3dpI1PcxsOxU9ngTVfkqNN7dZYVmQPfYq9bNijCBN6dysQB9vvDQRiwszRhMXgnLPs+aZkLRVEHmvdHebxisl7WM8UyvQl4En+Gyu0r1NKSL009RzHJRzLU4EaRJNaTwLgijTYQV6c7psvDQb5PalnpXzrNSbfUE0keq24LS5gvQxnp0V6KWjbn+ewXhCm1LkHDGPYDwjK9BLh3yxveA63e7V83Tj9Sq71WzxFU/Tz+LUeEZWoIfriN/a9TMrdji7RJbkgHXseXEoyH4SnTBMEH06rEA/ICAIg8sv0LEC3R0gCINL42EFujtAEAaXxsMKdHeAIAwujYcV6O4AQRhcGg8r0AGgFR2Md679AQBIAOMBwATAeAAwATAeAEwAjAcAEwDjAcAEwHgAMAEwHgBMAIwHABMA4wHABMB4ADAB/wPlbeSoxrWF7wAAAABJRU5ErkJggg==" />可以看到case when 返回的是boolean结果；再次无效
于是继续查看手册还有没有办法，if()函数也能进行分支判断，可以一试。
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
if()函数可以返回数值或者是字符
| 代码如下 | 复制代码 |
|
SELECT *,if(starttime < UNIX_TIMESTAMP(), 'starttime < UNIX_TIMESTAMP()', 'starttime > UNIX_TIMESTAMP()') as t |
|
结果还是语法错误，[Err] 1054 – Unknown column ‘t’ in ‘where clause’
把 and t去掉；sql语句是能正常执行的。结果如下
if()函数确实是执行了，但是返回的字符 别名为t，sql语句还是不认识。这个没搞明白。
无奈，只好求教于公司同事w；在简单说明后，他说项目里面有一处地方用到了这个，已经有了一个sql语句实现了；原来这个项目里面已经处理过这样的业务需求了，他说那时候第一次研究出来的，因为这个项目我是半路进来的，前面的开发都没参与；但隐约记得那一段时间他们组里的人都在说“写出一个牛x的sql排序了”，不过因为各种原因，我没去了解。哎，总归还是遇上了。
于是请w去讲解下这个处理方式。
在查看到项目源代码后，他说简而言之就是一个排序的实现，sql里面还是要进行两个状态的判断；
就是通过if()函数构建一个线性的排序
| 代码如下 | 复制代码 |
|
SELECT *,if( starttime < UNIX_TIMESTAMP(), starttime - UNIX_TIMESTAMP(), (starttime - UNIX_TIMESTAMP()) + 2000000) as t |
|
分析if()函数的作用。
为true
也就是已经开始记录的排序，执行 starttime – UNIX_TIMESTAMP() 按得到的值；starttime asc排序这样就得到了“最先开始的活动记录，也就是按starttime asc排序”列表
为false
执行 (starttime – UNIX_TIMESTAMP())+20000000
starttime – UNIX_TIMESTAMP()这样得到了 “starttime距离现在最近的，也就是马上将要开始的starttime”记录列表；由于 “未开始记录的”starttime – UNIX_TIMESTAMP() 得到的值可能小于“已开始记录的”starttime – UNIX_TIMESTAMP() 的值，这种情况下排序就混乱了，所以要加上一个很大的值20000000避免这种情况，这样就能保证未开始记录的starttime永远大于已开始记录的 starttime值，这样未开始的值记录排在了已开始记录之后了。
( 图上的时间是假定的，就是为了说明已经开始的记录是哪几条。其实执行的时间也是算得出来的:-) )
这里我们的需求就快要实现了，可以看到已开始记录是按starttime asc排序的。也就是得到的是“最先开始的活动记录”，而我们要的是“刚刚开始的活动记录”，实现这个其实很简单了
starttime < UNIX_TIMESTAMP() ，那么starttime – UNIX_TIMESTAMP() 则是一定为负数的；starttime越大，得出的绝对值越小。反之starttime越小，得出的绝对值则越大，但加上负号，则是starttime越小的，得出的负值越小了，最后sql语句都是asc排序的。结果集里面已开始的记录也就是asc排序的。
所以如果要starttime 正序desc排序，那就是 UNIX_TIMESTAMP() – starttime 表达式了，starttime越大的，得到的值越小；starttime越小的，得到值越大；因为最后sql语句是asc排序，结果集里面已开始的记录从排列上来说是desc倒序的。
| 代码如下 | 复制代码 |
|
SELECT *,if( starttime < UNIX_TIMESTAMP(), UNIX_TIMESTAMP() - starttime, (starttime - UNIX_TIMESTAMP()) + 20000000) as t FROM `t_time` where endtime > UNIX_TIMESTAMP() order by t asc; |
|
可以看到id为2的记录排到第一了。
这个业务需求在与当前时间有关且记录是否过期进行排序的列表中常应用到，一次请求后数据处理过程中；可以用endtime与当前时间进行对比，对于已过期的记录进行标记，这样显示时可以很明了了。
相关文章
- 射雕手游悬赏中都渔水行首领怎么打 射雕手游悬赏中都渔水行首领攻略方法 04-20
- 江南百景图上官婉儿主题活动内容有什么 江南百景图上官婉儿主题活动内容介绍 04-20
- 原神仆人烬火音乐活动怎么参与 原神仆人烬火音乐活动参与方法 04-20
- 射雕手游悬赏中都渔水行首领怎么打 04-20
- 射雕六韬怎么收集 04-20
- 射雕折梅遥寄乡愁漫漫成就怎么获取 04-20