背景:
任务管理系统中,任务列表查询的时候需要在一个table中尽心如下排序.首先,按照任务状态,任务状态是已领取的始终排在前面,其后是各种状态的任务,这些按照更新时间进行排序.所以首先想到的是将两个查询结果集进行排序然后union.但是在这个过程中会出现很多坑,在此进行总结.
(SELECT t.* FROM task t WHERE taskState = '002002' AND t.performerId = '13c7c0ae2ff5440880dc572da35722b8' ORDER BY t.createTime DESC,SUBSTR(t.taskTitle FROM 1 FOR 9) ASC)
UNION
( SELECT t.* FROM task t WHERE taskState != '002001' AND taskState != '002002' AND taskState != '002005' AND t.performerId = '13c7c0ae2ff5440880dc572da35722b8'
ORDER BY t.updateTime DESC,t.createTime DESC,SUBSTR(t.taskTitle FROM 1 FOR 9) ASC
)
首先,很多帖子说需要在union的两个子句中需要再查询一次,嵌一个select查询,其实不用使用()同样可以将order by和union隔离开,多一次结构查询就多一次性能消耗.
其次,在两个子句中排好序一旦union顺序又乱了,根本不像我们想象的那样将数据拼装.解决方法是在两个子句中加入一个排序字段,最外层再写一层查询.
SELECT * FROM(
select * from (
SELECT t.* ,00 as sort FROM task t WHERE taskState = '002002' AND t.performerId = '13c7c0ae2ff5440880dc572da35722b8'
ORDER BY createTime DESC ,SUBSTR(taskDesc FROM 1 FOR 9) ASC;
) as table1
UNION ALL
select * from
(
SELECT t.* ,11 as sort
FROM task t
WHEREtaskState != '002001' AND taskState != '002002' AND taskState != '002005' AND t.performerId = '13c7c0ae2ff5440880dc572da35722b8'
ORDER BY updateTime DESC ,SUBSTR(taskDesc FROM 1 FOR 9) ASC
) as table2
) aaa ORDER BY sort ASC
另外介绍一下,field这个函数,书写格式为格式: order by field(字段,值1,值2,...)
这个函数的意思是按照字段首先排值1,然后排值2,依次排序.这个函数在一个查询中可以很方便的使用,显然我们上面的案例不适用