MySQL查询可多选随机结果 - MySQL query for multi select of random results

MySQL query for multi select of random results

本文关键字：随机结果查询 MySQL | 更新日期: 2023-09-27

我有一个查询，它根据特定的县随机选择4个特色属性。

如果某个县有4个以上的特色属性，则查询会正确提取4个。。。然而，当这个县只有不到4个。。。如何提取县内的属性（如果找到），然后随机包括县外的记录，从而总共显示4个结果？

以下是我的查询示例。。。

SELECT
    `name`,
    `sleeps`,
    `town`,
    `county`,
    `price`,
    FLOOR(1 + RAND() * p.id) AS 'randnum'
FROM
    `properties_featured` AS `f` LEFT JOIN `properties` ON f.code=prop.code,
    (SELECT MAX(t.id) - 1 AS 'id' FROM `properties_featured` AS t) AS p
WHERE prop.status='on' AND `county`='Dorset'
ORDER BY `randnum` LIMIT 4;

非常感谢

从WHERE子句中删除郡匹配，并使其成为ORDER BY的一部分。

SELECT
    `name`,
    `sleeps`,
    `town`,
    `county`,
    `price`,
    FLOOR(1 + RAND() * p.id) AS 'randnum'
FROM
    `properties_featured` AS `f` LEFT JOIN `properties` ON f.code=prop.code,
    (SELECT MAX(t.id) - 1 AS 'id' FROM `properties_featured` AS t) AS p
WHERE prop.status='on'
ORDER BY `county` = 'Dorset' DESC, `randnum`
LIMIT 4;