php – 单个mysql查询,用于选择25个记录(9 16),使用union all并

我不知道使用UNION ALL是否是一项硬性要求,但SQL已经有一个非常好的系统来过滤第二个查询中第一个查询的结果：它被称为UNION.你可以选择剩余的16个,取最好的200个中的9个和整个集合中的25个,然后将总结果限制为25.我假设UNION将从第二组中删除重复,而不是第一组.

尝试这样的事情：

SELECT * FROM (
    SELECT * FROM (
        SELECT tiles.*,COUNT(tile_appreciations.tile_id) AS appreciation_count 
        FROM tiles LEFT JOIN tile_appreciations ON tile_appreciations.tile_id = tiles.id INNER JOIN users ON users.id = tiles.user_id LEFT JOIN user_settings ON user_settings.user_id = tiles.user_id 
        WHERE tiles.view_mode = 'PB' AND users.status = 'Y' AND tiles.moved_stat = '1' AND user_settings.public_profile = 'Y' 
        GROUP BY tiles.id 
        ORDER BY appreciation_count DESC LIMIT 200
    ) as best200
    ORDER BY RAND() 
    LIMIT 9
) UNION (
    SELECT tiles.*,COUNT(tile_appreciations.tile_id) AS appreciation_count 
    FROM tiles LEFT JOIN tile_appreciations ON tile_appreciations.tile_id = tiles.id INNER JOIN users ON users.id = tiles.user_id LEFT JOIN user_settings ON user_settings.user_id = tiles.user_id 
    WHERE tiles.view_mode = 'PB' AND users.status = 'Y' AND tiles.moved_stat = '1' AND user_settings.public_profile = 'Y' 
    GROUP BY tiles.id 
    ORDER BY RAND() 
    LIMIT 25
)
LIMIT 25;