制定查询优先级队列表的 SQL [英] Working out the SQL to query a priority queue table

问题描述

我正在实现一个小队列来处理哪个进程首先运行.我正在使用数据库中的表来执行此操作.这是表的结构(我在 SQLite 中模拟它):

I am implementing a small queue to handle which process gets to run first. I am using a table in a database to do this. Here is the structure of the table (I'm mocking it up in SQLite):

        "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
        "identifier" VARCHAR NOT NULL ,
        "priority_number" INTEGER DEFAULT 15,
        "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "description" VARCHAR

我正在尝试编写 SQL 来告诉我接下来可以运行哪个进程的行.以下是一些示例数据:

I am trying to write SQL to give me the row of which process can run next. Here is some sample data:

id  identifier  priority_number timestamp   description
1   test1   15  2009-01-20 17:14:49 NULL
2   test2   15  2009-01-20 17:14:56 NULL
3   test3   10  2009-01-20 17:15:03 NULL
4   test4   15  2009-01-20 17:15:08 NULL
5   test5   15  2009-01-20 17:32:23 NULL
6   test6   14  2009-01-20 17:32:30 NULL
7   test7   7   2009-01-20 17:32:38 NULL
8   test8   20  2009-01-20 17:32:57 NULL
9   test9   7   2009-01-21 13:47:30 NULL
10  test10  15  2009-01-21 13:50:52 NULL

如果我使用这个 SQL，我可以按正确的顺序获取数据:

If I use this SQL, I can get the data in the proper order:

select * from queue_manager order by priority_number, timestamp;

这将为我提供顶部具有最低优先级编号(最重要)的项目，并且在这些优先级编号中，最早进入队列(按时间戳)在顶部.

This will give me the item with the lowest priority number (most important) at the top, and in those priority numbers, the earliest into the queue (by timestamp) at the top.

我可以运行此查询，并且只获取第一行，但我宁愿使用 SQL 查询来执行此操作，该查询将为我提供位于队列顶部的进程的一行(在上面的示例数据中, id=7 的行).

I could run this query, and only take the first row, but I would rather do this with a SQL query that would give me the one row of the process that is at the top of the queue (in the example data above, the row with id=7).

我尝试进行自连接和子查询，但我一定有心理障碍 - 我似乎无法正确处理.

I tried doing self joins and sub queries, but I must be having a mental block - I just can't seem to get it right.

提前致谢！

编辑

我忘了提到我正在寻找一个独立于数据库的查询.我在 SQlite 中对此进行了模拟，但很有可能我会在 DB2 或 Oracle 中实现它.我曾想过在我的查询中使用limit 1"类型的运算符，但这在不同的数据库引擎之间是不同的.

I forgot to mention that I am looking for a database-independent query. I am mocking this up in SQlite, but there is a good possibility I will implement this in DB2 or Oracle. I had thought to use a "limit 1" type operator on my query, but that is different between different database engines.

推荐答案

看看是否可行:

select * from queue_manager where priority_number = 
(select min(priority_number) from queue_manager) and  
timestamp = (select min(timestamp) 
from queue_manager qm2 
where qm2.priority_number = queue_manager.priority_number)

这篇关于制定查询优先级队列表的 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！