假设我有以下MySQL(InnoDB)表:
+-------------------------------------------------------------------+
| table "tasklist" |
+----+------------------+----------+---------------------+----------+
| ID | TaskName | Category | Date_time | Priority |
+----+------------------+----------+---------------------+----------+
| 1 | cleanup | system | 2019-06-02 03:30:00 | 5 |
+----+------------------+----------+---------------------+----------+
| 2 | create_user | system | 2019-03-23 11:56:10 | 5 |
+----+------------------+----------+---------------------+----------+
| 3 | send_invoice | system | 2019-03-23 11:56:17 | 6 |
+----+------------------+----------+---------------------+----------+
| 4 | perform_selftest | system | 2019-06-25 06:54:11 | 1 |
+----+------------------+----------+---------------------+----------+
| 5 | add_destination | map | 2019-02-15 16:21:04 | 2 |
+----+------------------+----------+---------------------+----------+
| 6 | verify_VIN | chassis | 2019-01-04 09:35:49 | 5 |
+----+------------------+----------+---------------------+----------+
我想编写一个查询,选择一个符合以下所有条件的记录(请注意,引号之间的值是示例,并将在现实世界中进行参数化):
- 类别“系统”
- “ 2019-01-01”到“ 2019-07-01”之间的日期时间
- 此子集中的优先级最高,最接近“ 2”(允许2本身,但不允许更高,例如1)
在这种情况下,有4条记录与前两个条件匹配。但是,在这4个中,只有2个匹配优先条件。因此,此处应返回记录#1和#2(仅记录)。
通过搜索该网站,我编写了以下查询,该查询似乎有效,但是它很丑陋,并且我认为它可以提高性能:
SELECT * FROM tasklist
WHERE category='system'
AND (Date_time BETWEEN '2019-01-01' AND '2019-07-01')
AND Priority=(
SELECT MIN(Priority) FROM tasklist
WHERE category='system'
AND (Date_time BETWEEN '2019-01-01' AND '2019-07-01')
AND Priority >= 2
)
ORDER BY Date_time DESC
有人知道更好的方法吗?