我有一个表,我想根据优先级和日期获取记录,我通过这个查询获得
SELECT *
FROM tbadv
WHERE advstrdat < CURDATE()
ORDER BY advenddat DESC,
advpri=1 DESC,
advpri=2 DESC,
advpri=3 DESC,
advpri=4 DESC,
advpri=5 DESC
LIMIT 1
但如果存在多个具有相同优先级的记录,那么我希望从中随机记录
告诉我我更喜欢ORDER BY RAND()
还是使用JOIN
。
这是我的表中的列:
advid
advtit
advdes
advimg
advurl
advloc
advstrdat
advenddat
advpri
如有任何帮助和建议,我们将不胜感激。
您想如何解决关系还不太清楚。
总之:
SELECT *
FROM tbadv
WHERE advstrdat < CURDATE()
ORDER BY advenddat DESC, advpri BETWEEN 1 AND 5 DESC, advpri DESC, RAND()
LIMIT 1
这将根据advenddat
的日期检索最新记录,而不考虑优先级,即,比高优先级记录更新的低优先级记录将优先(因此,请检查advenddat
是否为日期而不是时间,或者,如果是,则使用数据初始化。否则,很少会有具有相同advenddat
的记录,根据其他字段进行排序)。
然后将选择优先级在1和5之间的那些记录。其中,优先级较高的将优先考虑。
如果两条记录在1和5之间(比如3)具有相同的advenddat
和advpri
,那么平局将随机打破。
出于性能原因,您希望在advstrdat, advenddat, advpri
上按以下顺序设置覆盖索引:
CREATE UNIQUE INDEX tbadv_ndx ON tbadv ( advstrdat, advenddat, advpri, advid );
如果表很大,并且匹配了许多记录,则可以通过解耦记录选择(只需要主键)和实际记录检索来实现最佳性能:
SELECT tbadv.*
FROM tbadv
JOIN (
SELECT advid
FROM tbadv
WHERE advstrdat < CURDATE()
ORDER BY advenddat DESC, advpri BETWEEN 1 AND 5 DESC, advpri DESC, RAND()
LIMIT 1 ) AS p
USING ( advid );
+----+-------------+------------+--------+---------------+-----------+---------+-------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+-----------+---------+-------+-------+-----------------------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | tbadv | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | tbadv | range | tbadv_ndx | tbadv_ndx | 4 | NULL | 60924 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+--------+---------------+-----------+---------+-------+-------+-----------------------------------------------------------+
上面是一个大约有13万行的示例表,非常不平衡(所有记录都有有效的curdate
,所有记录都是有效的priority
,等等)。然而,它在几毫秒内完成。
"无效"记录会被快速过滤,因此性能与单个查询相当,只是只检索到advid
:这里,只有七条记录匹配:
+----+-------------+------------+--------+---------------+-----------+---------+-------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+-----------+---------+-------+------+-----------------------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | tbadv | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | tbadv | range | tbadv_ndx | tbadv_ndx | 4 | NULL | 7 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+--------+---------------+-----------+---------+-------+------+-----------------------------------------------------------+
不同的顺序
如果你想让advpri
优先于advenddat
,也就是说,今天到期的优先级为5的记录必须优先于优先级为4的记录,即使它明年到期,你只需要颠倒advpri
和advenddat
:的位置
ORDER BY advpri BETWEEN 1 AND 5 DESC, advpri DESC, advenddat DESC, RAND()
现在优先级1-5优先,然后优先级0、6、7和1-5范围之外的其他优先级;并且它们首先达到最高优先级(即5)。如果两个记录具有相同的优先级,那么预期寿命较长的记录优先。只有当预期寿命相同时,选择才是随机的。(您还必须交换索引创建语句中的字段位置)。
SELECT * FROM tbadv where advstrdat<curdate() ORDER BY advenddat DESC, advpri DESC, RAND() DESC LIMIT 1