我在MySQL中使用FIND_IN_SET,如下所示:
SELECT
color, b.id
FROM cards b
where FIND_IN_SET(b.color, 'gold,gold,bronze,silver,silver') > 0
GROUP BY b.id
ORDER BY RAND()
limit 5;我得到的输出如下:

但是,我希望以与提供给FIND_IN_SET的参数字符串相同的顺序获取卡片。
gold, gold, bronze, silver, silver因此,第一个输出行应该是金色的,然后是另一个金色的,然后是青铜色的,依此类推-在相同的序列中,没有任何随机搜索。
我不能自连接,因为它是一张大桌子。
发布于 2020-02-15 09:03:29
您需要使用3个查询的UNION来查找随机的‘金牌’、‘银牌’和‘铜牌’奖牌获得者,然后使用FIELD对这些结果进行排序
SELECT color, id
FROM (
(
SELECT color, id
FROM cards
WHERE color = 'gold'
ORDER BY RAND()
LIMIT 2
)
UNION ALL
(
SELECT color, id
FROM cards
WHERE color = 'bronze'
ORDER BY RAND()
LIMIT 1
)
UNION ALL
(
SELECT color, id
FROM cards
WHERE color = 'silver'
ORDER BY RAND()
LIMIT 2
)
) m
ORDER BY FIELD(color, 'gold', 'bronze', 'silver')以下是一种可以将其实现为PHP/MySQL组合的方法:
$pack_sql_comma_separated = 'gold,silver,gold,gold,silver,gold';
$colors = explode(',', $pack_sql_comma_separated);
$counts = array_count_values($colors);
$sql = "SELECT color, id FROM (";
$subqueries = [];
foreach ($counts as $color => $count) {
$subqueries[] = "(
SELECT color, id
FROM cards
WHERE color = '$color'
ORDER BY RAND()
LIMIT $count
)";
}
$sql .= implode(' UNION ALL ', $subqueries) . ') m';
// run SQL query
// ...
// assume all query results read into $rows according to medal colour, something like this
$rows = array('gold' => array(array('id' => 20),
array('id' => 5),
array('id' => 4),
array('id' => 27)
),
'silver' => array(array('id' => 19),
array('id' => 11)
)
);
//display in required order
foreach ($colors as $color) {
$card = array_shift($rows[$color]);
echo "$color {$card['id']}\n";
}https://stackoverflow.com/questions/60235106
复制相似问题