单个mysql查询，用于选择25个记录（9 + 16），使用union all并基于2个不同的条件

I need to fetch 25 records using mysql out of which first 9 must be based on the descending order of likes count (chosen randomly) from 200 top appreciated and balance 16 randomly from the remaining items (excluding 9, that are already filtered). Is it possible to do this using a single mysql query? Any help will be appreciated.

我需要使用mysql获取25条记录,其中前9条必须基于喜欢计数的降序(随机选择)从200顶部升值和从剩余项目中随机平衡16(不包括9,已经过滤)。是否可以使用单个mysql查询执行此操作?任何帮助将不胜感激。

Here is my query...

这是我的查询......

(SELECT * FROM (SELECT tiles.,users.first_name,users.last_name, users.mosaicname,users.country,users.city,users.state,users.profile_image,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 t1 ORDER BY RAND() LIMIT 9) UNION ALL (SELECT tiles.,users.first_name,users.last_name,users.mosaicname,users.country,users.city,users.state,users.profile_image,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 16)

(SELECT * FROM(SELECT tiles。,users.first_name,users.last_name,users.mosaicname,users.country,users.city,users.state,users.profile_image,COUNT(tile_appreciations.tile_id)AS appreciation_count FROM tiles LEFT JOIN tile_appreciations ON tile_appreciations.tile_id = tiles.id INNER JOIN用户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 t1 ORDER BY RAND()LIMIT 9)UNION ALL(SELECT tiles。,users.first_name,users.last_name,users .mosaicname,users.country,users.city,users.state,users.profile_image,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 16)

1 个解决方案

#1

I don't know if using UNION ALL is a hard requirement, but SQL already has a very good system for filtering out results from the first query in the second query: it is called UNION. You can choose the remaining 16 by taking the union of 9 of the best 200 and 25 of the whole set and then limiting the total result to 25. I'm assuming here that UNION will remove duplicates from the second set and not the first.

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

Try something like this:

尝试这样的事情:

SELECT * FROM (
    SELECT * FROM (
        SELECT tiles.*,users.first_name,users.last_name, users.mosaicname,users.country,users.city,users.state,users.profile_image,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.*,users.first_name,users.last_name,users.mosaicname,users.country,users.city,users.state,users.profile_image,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;

1 个解决方案

#1

更多相关文章

随机推荐