根据重要性多次出现元素的MySQL结果

Question

谁能告诉我是否可以根据每行的重要性获取 mysql 条目列表。假设我在一个表中有 4 个条目(id、名称、重要性)。 0 = 最高重要性。

id | file      | importance
---|-----------|-----------
1  | movie.mp4 | 0
2  | image.jpg | 1
3  | movie1.mp4| 2
4  | movie2.mp4| 2

想要创建一个包含 20 个条目的数组，这些条目用 4 个元素的结果填充(例如 5*movie.mp4、3*image.jpg、2*...、2*...)，但是诀窍是元素必须随机放置在数组中，而不是一个接一个地放置。

$file[] = movie.mp4;
$file[] = movie2.mp4;
$file[] = image.jpg;
$file[] = movie.mp4;
$file[] = image.jpg;
$file[] = movie1.mp4;
$file[] = movie.mp4;
$file[] = image.jpg;
$file[] = movie.mp4;
$file[] = movie1.mp4;
$file[] = movie.mp4;
$file[] = movie2.jpg;

希望你明白我的意思，否则请问:)

Answer 1

SELECT draw_bucket.file
FROM (
    SELECT row,
    FLOOR( RAND() * ( SELECT SUM( POW( 2, 2 - importance ) ) FROM files ) ) AS rnd
    FROM ( 
       (SELECT 1 AS row) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4) UNION
       (SELECT 5) UNION (SELECT 6) UNION (SELECT 7) UNION (SELECT 8) UNION
       (SELECT 9) UNION (SELECT 10) UNION (SELECT 11) UNION (SELECT 12) UNION
       (SELECT 13) UNION (SELECT 14) UNION (SELECT 15) UNION (SELECT 16) UNION
       (SELECT 17) UNION (SELECT 18) UNION (SELECT 19) UNION (SELECT 20)
    ) AS row_nums
) AS rows
INNER JOIN (
    SELECT @row := @row + 1 AS row, file
    FROM ( 
       (SELECT 1 AS cnt) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4)
    ) AS cnt
    INNER JOIN files
    ON( cnt.cnt <= POW( 2, 2 - files.importance ) )
    INNER JOIN ( SELECT @row := -1 ) AS INIT
) AS draw_bucket
ON( rows.rnd = draw_bucket.row )

number of (SELECT 1), (SELECT 2), ... in the row_nums subquery should equal the number of entries you want and in cnt 子查询应该等于 2 ^ minimumPriority。

您可以通过在 PHP 中完成大部分工作来简化此操作(例如)。执行 SELECT * 并像这样填充一个数组:

$items = array(
    array( 'file' => 'movie.mp4', 'importance' => 0 ),
    array( 'file' => 'image.jpg', 'importance' => 1 ),
    array( 'file' => 'movie1.mp4', 'importance' => 2 ),
    array( 'file' => 'movie2.mp4', 'importance' => 2 )
);

然后用PHP画图:

$minImportance = 2;

$drawBucket = array();
foreach( $items as $index => $item ) {
    for( $i = 0; $i < pow( 2, $minImportance - $item['importance'] ); ++$i ) {
        $drawBucket[] = $index;
    }
}

$output = array();
for( $i = 0; $i < 20; ++$i ) {
    $randomIndex = mt_rand( 0, count($drawBucket) - 1 );
    $output[] = $items[ $drawBucket[$randomIndex] ][ 'file' ];
}

var_dump( $output );