我有一个表,其中包含一个进程要执行的任务列表。每个任务只处理与输入状态匹配的项目,当任务完成时,它会将项目的状态更改为输出状态。
为了跟踪这些任务,我使用了这样一个表。
CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(80) NOT NULL,
`job_type` varchar(45) NOT NULL,
`input_status` varchar(45) DEFAULT NULL,
`output_status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
)
任务的状态形成一系列事件。以下是的示例
NULL -> NEW
NEW -> CREATE
CREATE -> INSPECT
INSPECT -> VERIFY
VERIFY -> PUBLISH
在现实生活中,任务清单很长注意:我不知道不同状态的类型,这些用户定义的值。
当我使用input_status
的顺序查看表时,记录会以错误的顺序显示任务。按input_status
和output_status
排序也不起作用(显然)。
如何对首先是null
,然后是input_status
到output_status
的链的表进行排序?
我想我必须创建一个虚拟字段来保存一个额外的排序值,但我不确定它应该是什么或是计算出来的。
这是我迄今为止尝试过的,但没有效果。
SELECT *,
(SELECT input_status FROM tasks AS parent
WHERE parent.output_status = tasks.input_status
) AS sorted
FROM tasks
ORDER BY sorted, input_status;
您可以构建一个应用排序的case语句(但不确定确切的语法):
SELECT * FROM tasks
ORDER BY
CASE WHEN input_status IS NULL THEN '_'
ELSE output_status END ASC;
当然,您必须根据您的排序需求调整案例语句。
我不确定您是否定义了状态值的主关系。若你们做到了,那个么查询就会更容易。
主Staus保持status_id
和status_text
(null
、new
、verify
等)。子tasks
要从主机引用以获取输入和输出状态id值。
实际上,您需要一个查询来完成它。您可以将output_status
视为ID,将input_status
视为PARENT_ID。因此,您需要找到完整的PARENT->CHILD路径来分配订单号。
使用递归CTE在MS SQL中可以简单地完成,但对于MySQL来说,我知道这并不简单。
尝试使用这些UDF来创建排序字段,例如[null->stat1->stat2->..]
另一种方法是,如果有一个固定的最大状态计数,你可以使用LEFT JOIN连接以前的记录,找到要排序的路径
SELECT tasks.*
FROM tasks
LEFT JOIN tasks t1 on t1.output_status=tasks.input_status
LEFT JOIN tasks t2 on t2.output_status=t1.input_status
LEFT JOIN tasks t3 on t3.output_status=t2.input_status
ORDER BY tasks.title,t3.output_status,t2.output_status,t1.output_status
这里还有需要考虑的链接:
- 递归self查询