id name time
1 a 21:00
2 a 05:00
3 b 03:00
4 a 19:00
5 b 18:00order by name,time
但是time需要做个处理,就是00点以后的时候大于00前的查询结果是
4 a 19:00
1 a 21:00
2 a 05:00
5 b 18:00
3 b 03:00有没有人帮忙给个
1 a 21:00
2 a 05:00
3 b 03:00
4 a 19:00
5 b 18:00order by name,time
但是time需要做个处理,就是00点以后的时候大于00前的查询结果是
4 a 19:00
1 a 21:00
2 a 05:00
5 b 18:00
3 b 03:00有没有人帮忙给个
order by name, decode(sign(12 - time), -1, time, time + 24)
WITH t AS(
SELECT 1 AS ID ,'a' AS NAME,'21:00' AS TIME FROM dual UNION ALL
SELECT 2 AS ID ,'a' AS NAME,'05:00' AS TIME FROM dual UNION ALL
SELECT 3 AS ID ,'b' AS NAME,'03:00' AS TIME FROM dual UNION ALL
SELECT 4 AS ID ,'a' AS NAME,'19:00' AS TIME FROM dual UNION ALL
SELECT 5 AS ID ,'b' AS NAME,'18:00' AS TIME FROM dual
)
SELECT t.* ,CASE WHEN substr(TIME,1,2) < 10 THEN 1 ELSE 2 END AS flag FROM t t ORDER BY NAME,flag DESC,TIME;
2 order by name asc, time desc; ID NAME TIME
---------- ---------------------------------------- ----------------------------------------
1 a 21:00
4 a 19:00
2 a 05:00
5 b 18:00
3 b 03:00