sql按时间排序一些方法介绍

作者：袖梨 2022-06-29

在项目中遇到了这样的一个需求

“在用户参与的一个活动列表中，获取当前时间中刚刚开始了(也就是刚才开始的，不是最先开始)的一条活动；如果当前时间没有活动开始，则获取将要开始的一条活动记录”且必须在一次数据库查询操作中完成。

也就是必须要在数据库中做活动的状态区分。

现在具象化一点的理解这个需求，有这样一个表t_time

里面只有三个字段

id，starttime，endtime

psY/9XnDbGeT/6yFqeUoTjasdNHY5x6LD+6EmYKmCC3aZqznbb33HPXj4X0XXdn//Zn+6iX+L0LcNGr32uiBYQrSHaTdSm83g77jaGf4Umi8ZQEK+AcFvotoqDNQpiunKKEc4pWcfNztGpnGH/CY5rZ5rjTvMnu8HoVGMPdnbHHbYL48k6X6pRP2uxFCxq2+ZkND7uo2T0nnPomsXoOsP+z0ssyofDvnzxTETXdT/4/p9UjOoFtBYIEgqSPcsrIExCzOnG6zy1EW6HqbZfzmjGiSZa+XCU6KDR4cLdo6TmUJFTOG52/k2Na7QK70HMpk0NbXwsor4odLA8uKnD9M5e3d9yKkY7bBOPUfbsMg5hPPxo5alRsspHc794/kRE13V//Ed/WDGqF9BaIEgoSPYsr0Bq5grvsphQahIR9xq39bj2DqKY7Dw7YaBozqiwevTs0BbHzebff94Xo1jOB7vIqXPPeM7Yi9wGsghEh+yOlm6isFHdLI77/NmvRHRd973vfbdiVC+gtUCQUJDsWV6B1HPwsM9kx83ea3TcabOS5a7oAc7luNH+3uLDOJzXIeqIdjHFM6pRj/VYMxMZok4frTx14BMMz+64ogZjH3FQ2TqjnVMeP/bRDBP859NHIrqu+853vl0xqhfQWiBIKEj+WiqPfdpqynHFXCN23MopdX/HHZtzbE9Lh+zBptpDA846StjZUsyollGNE9q36QMfVefkx8u7d9SlZ7xapzluB7Bysmd5UcRz7X0cd8L8m9pr8jqgcccdZYpjydbgDZp132x5djPTOft6smYvBt3fcUXjqJZUQv2sa9RpJnLu77jfl3Rd963f+f2Ksdls6hbQWiBIKEj2LK+MvpKHk0JqWtSTl3BcXcZw9LC/t2/KvKNPL1LdLE8IopVYcopswmM0KdHs3ikOOTyi1I7hWNHDT+1oN1ddmzFJeNe8jms84beDR2pyHnGOGV3fGxPHXVcgSChI9iyvhtGN5s1v7K/9b7JFHR5ah5RQYz3G8lhkc6ZKzT644hHXZ4L9aYHl7DL2EQcrLgchZmioIolIbgDHXVkgSCiI5UQHAKjO147rXNx6cdzWAkFCQWpfRAlGrT7tfQBgtXzjuFHTLem42+02Op+WGa5XxLsZbUzlMe41rWUVgmT3mivPUJBtm0Qd1/jiIa/rAhwi37hs1HSLOe6umrB9IYPxhgu3w2K2g9k/myfVeXKfVQiyxF6pPENBlrgwppP6GIXl4xX4K8BB84XFhqb7cNa4dquwtItGnTnVZxWCjDqQ7aE67jbx0d+sm0b3AoADwn9V2TPdB+W4O0WG27rFYjyWzMNGPdYqBPnWl26aFS3cjrYIQWIndlV67xz1Kc3oNgAcEJH3cYfbD8pxhfFkTcKeOdzX0rJqQXSf6PbwHNX1tOi4O+xr3OhbvABwcLDGnd9gdOZwX0vLWgRJJTEKaBHzm7Gc2zrXqOPqb/UN++hv+GG9AAcE7+PGty0tFpOYllnkb1mQsVKIbW9DCNKiM6XWrE0VCQDFaeuzyttSb1uGw83VErGE+TI3Loh3bi0tUS/I13c08o2a6GeSU447bJ/w/V0AWBUNfR83GvzgA4JkBYmd121br3cz+4UiADgI+M2plQWChILIE7ye9U5w3LAF0wU4IPhd5ZUFgoSC2M704tYrXknO2iqOC3CI4LgrCwQJBRl5ypey3tQoqcZG3ocGgMXAcVcWCBIKMvXcx+EAoCiuy7EBaJs5rgOsFwAWJzPFdF1Xpo4U88ynBwSCeMwsiMV6MWYAmIT7LMFxWwNBPJYSRFgvC2IAmIT7dH8vAsdtDQTx WFwQz1+z/5Dg8AIAZsLd3d2KwHFbA0E8ygliMaTD86fDOyKAerjbjzciijmu+3+89uXmU28srwD3JaLI4U3jXtNadjQuSHavufL0lH4Kon338Pzp8I4IoB7u5sN7EWUc15tqh3ctNJ+G87u3HVrdcPbP5kl1ntynp2VBltgrlaenFcd1B/q/Cg7viADq4TbXVyIO1XHDgbx2YRWWdtGoM1c0mLkE0XuFu6/JcR/gu56Hd0QA9XDvri5FFH4ft9iSLjpW6mXMVIvIMyrzsFGPtV2JIKNEMzpueUEiZM1V+NMoqx7b2V7GWHBcgPlwb9+8FlHScaPrmFpLurBFm4Q989glnZetfUEsLdltYbHFBImQXctO9idLQvugOC5Ak7jLi1ciSn5yKtrevsHguKJPqmUfMVtx3KjvtuC483okjgswH+7i/KWI8u/jepQxmH1ccKy1rMJx5yp7rMsO17iWPNuhINlXYrORJbujTiLunea40TLCjX3AcQHmw52fnYgo5ripVxEX/WiuN4/P0rINLGHGzNsVCrKcRDvmFGR/wy7suNvAX70++/sljgswH+7s9FgEv4DRGgji0cRnlYcdJuwuVtijHHfy2l3XDAAz4U5PjkTguK2BIB5reh/XuMYd9Rp49jVkHBegGdyL4ycicNzWQBCPNX1WeYJ7Zd/HDTdmXOBGBwWAqbjnR49F4LitgSAeFRxXd5h8b3YX8apyaqWL4wK0hHv29JEIHLc1EMSjLUH2dFztlxPe68VxAVrCdTk2AG1T+yIaUN5xWeMCrIfM5dR13XVVNptN3QJaA0E8DtZxjR/LCr8dxPu4AK3iPktw3NZAEI/mHPfwAgBmwn26vxeB47YGgni05bgAAGnc3d2tCBy3NRDEA8cFgLXgbj/eiCjmuP1v+IXz6XIjigLcl6SKnGUvY8sOBPHKxnEBYC24mw/vRZRxXG9i9ebThUZMDbrbDmf2oWFk95q9Tw+CeGXguACwFtzm+kpE+VeVyzhuOJDXLgwm2hIuEKdlTvXpaVkQce+igtS+iAAATLh3V5ciSjpu6jXD5YYTBYjXQrPeMzbzsDE7VvuCRFtGZb4eKUjtiwgAwIR7++a1iAe7xg1bXLDw0v1TmcN9LS097QsStiwtCF9iAYBV4C4vXonAca/lpB9NguNm79V5Rjvu1+cy3x8FgKZxF+cvRRzqJ6fEoNNsYLnMKxXEvj2b435zUmO9ANAi7vzsRMShfjso+8ahpcV9ybDb7GP1tCxIquylBUmf3fxwEgA0hDs7PRbBL2C0BoJ4WD85hfUCQG3c6cmRCBy3NRDEY/RnlbFeAKiEe3H8RASO2xoI4jH920FYLwCUxT0/eiwCx20NBPGY4fu4o6wXhwaAqbhnTx+JwHFbA0E85vwFDIv1siwGgKm4LscGoG0WuCwS1jvhw8+i56h/TBstxjgoTxEA2iBzKXZdV6aOFIvMp2sGQTyWFcTzvwn/rX1sn1R/u+OO8mYAKIj7LMFxWwNBPMoJYlmJim7CpCc4ruimnyUAQD3cp/t7EThuayCIR4k1rndz1Ep3/zVudKxwI5XKYucAUAR3d3crAsdtDQTxKPeq8tiVa5/BMoru7/lr1mVTnXFcgKq42483Igo7rgtmhOXmU2+s/kcEvZvRxnn3MrbsaFwQL9WqBdmNuq/pzu64evRwRT62GABYDHfz4b2Iko5bcj4NZ3NvW7tIdq/Z+/S0LEiYar2C9MOPc1m7N49y8aiheuNGC+hb9O4AUAS3ub4SUcxxU9N6sSVdtpJU/+i90c6WzKk+PY0LMnvnioLshh/hi+G+3l8xSnTba0nZamp3u2EDQBHcu6tLEQ/KcaOvfIqWcK9pmYeNeqxt84JEH8d1CDJ5MZrKltoQnVPdPNv2+mQdVycHgFK4t29eiyjjuMOJ1bur1pIubAl9JbWXzqz9KTtWy4KkHscWBTEaZ7Sn3W7FdqpxbM5Rw+G4AFVxlxevRJRc40YXMS0bTGqvFg3GwCyCiIVvKvPigtid0r57Kk/YGN7MdojWMLwLxwVYJ+7i/KWIB/JZ5WmTvs6wROZt24JYbi4ryJ7mGqV3u2mZdTejKXpeKyqJtqesGgDK4s7PTkSUd9wyBhNdh83Sso35zVyZt80LMmwf3lxurDnNNUqfeZqRi57aXO0tljXucvoAgBl3dnosgl/AaA0E8VhQkOgrwFgXAEzFnZ4cicBxWwNBPBb830GpdhwXACbhXhw/EYHjtgaCeMwsSNZQsVsAmIp7fvRYBI7bGgjiMfP3cQEAFsM9e/pIBI7bGgjiMYMgeC0AFMF1OTYAbbPH6Y/XAkA5MtMNa9zWQBCPiYLgtQBQHPdZguO2BoJ4jBYErwWASrhP9/cicNzWQBCPEYLgtQBQFXd3dysCx20NBPEwCYLXAkADuNuPNyKK/e+gIcO7ljMY/dOD0ZLEjxqO2mufHzVsWZCwT31B8FoAaAZ38+G9iML/rS9kIYMJZ3NvOyxpaBipVGIjzDyqT0/Lgozts6wgeC0ANIbbXF+JOFTHFYNmDWbRzqk+PS0LIsouKgheCwBN4t5dXYoo/6qyd1dJg4m+8ilatgk/GJt52Cj67GhZkFTZ5QTBawGgYdzbN69FlP/klDel1lrShS3CaLMGY8wjWnpaFiS1VwlB8FoAaB53efFKBI67lZN+an0mMuO42TyjBcFrAWANuIvzlyLKv49bxWD2nPTFzWmZVyqIzrBE5i1flwKA9eDOz05EFFvjmt6lW3K4aS3D9uHN5cZqXJBo2WsUBABgdtzZ6bEIfgGjNRDEA0EAYC2405MjEThuayCIB4IAwFpwL46fiMBxWwNBPBAEANaCe370WASO2xoI4oEgALAW3LOnj0TguK2BIB4IAgBrwXU5NgBtU/siAgAwkfnpgK7rrn+zrRibzaZuAa0FgoSClLlUAAD2xH2W4LitBYKEgtS+iAAATLhP9/cicNzWAkFCQWpfRAAAJtzd3a0IHLe1QJBQkNoXEQCACXf78UZEMcftf8MvnE+XG1EU4L4kVeQsexlbViGIl6eMIAUvF/O/TNj9LyMRxpzevfYds7sDQHHczYf3Iso47nAuLmMw4WzubYeVhEWKvWbvswpBRJ5FBSl4uQR+OTmPuGnsudtOGXk0LY4LUBW3ub4SUdhxwyi2pLMbjLAlndySOdVnFYJkj30hQQpeLml7G5sn1SLWxKOWy9ndAaA47t3VpYhijiteMyxmMNFXPnWLMAl75mHjqgUReRYVpODlIg0v7DzhLt0zusYV91p6AkAp3Ns3r0WUX+N6U2qtJV3YEhZpd9yxeVYqiO6/nCAFLxfD+7LDziKPfURRQLQl3BHHBWgDd3nxSgSOO8EGcNxRmedx3OwnlQqEf22ZPzmVNUtxl3GNK/IAQCncxflLEQ/EcadN+hbLmTdz44JECy4gSMHLZbE1bsoRLWvcaJLUajhbGwAshjs/OxFxqN8OCoebqyW0hBkzI0hUkIKXy0zv49o7W9a4xleVoxsAUBB3dnosgl/AaC0QJBSk4OUy5uO+szuu5QXq7PaE2gBgJtzpyZEIHLe1QJBQkIKXy5iv1oyyZOOryvYkOC5Ae7gXx09E4LitBYKEghS8XOZYtkY7pD54ldolask4LkDbuOdHj0XguK0FgoSC1L6IEtgdN/wMlCXn2E9OhSMCQFncs6ePROC4rQWChILUvojSiM82pxaponP0c8vhdjRtdC8AKIvrcmwA2qb2RQQAYIJnuwAAACXAcQEAAEqA4wIAAJQAxwUAACgBjgsAAFACHBcAAKAEOC4AAEAJcFwAAIAS4LgAAAAlwHEBAABKgOMCAACUAMcFAAAoAY4LAABQgv8DnhMDrGw3GUwAAAAASUVORK5CYII=" />

我们假定现在每一条记录就是一个活动。

现在可以把需求抽象的分析为“当前时间大于starttime且小于endtime的按desc到序排列获取第一条记录，当前时间小于starttime且小于endtime的按asc正序排列获取第一条记录”且同样是要在一条sql语句中完成。

于是开始进行sql编写，在sql要完成两种状态的辨别，那就肯定是需要条件分支类似的语句控制。

mysql中的条件语句分支，常用的就是if ,case when 语句。

在找到if then 的用法后，开始第一次if then语句的测试

代码如下

复制代码

if (SELECT * FROM `t_time` where starttime < UNIX_TIMESTAMP() and endtime >UNIX_TIMESTAMP())
then ( SELECT * FROM `t_time` where starttime < UNIX_TIMESTAMP() and endtime >UNIX_TIMESTAMP() order by starttime desc )
else if(SELECT * FROM t_time WHERE starttime > UNIX_TIMESTAMP() and endtime > UNIX_TIMESTAMP())
then ( SELECT * FROM `t_time` where starttime > UNIX_TIMESTAMP() and endtime >UNIX_TIMESTAMP() order by starttime asc )
end if;

结果是；华丽丽的报错啊！！！

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() and (case when starttime < UNIX_TIMESTAMP() then starttime < UNIX_TIMESTAMP() else starttime > UNIX_TIMESTAMP() end) order by starttime desc;

结果

但是还是没有达到预期效果，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 FROM `t_time` where endtime > UNIX_TIMESTAMP() and t order by starttime desc;

结果还是语法错误，[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 FROM `t_time` where endtime > UNIX_TIMESTAMP() order by t asc;

分析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的记录排到第一了。