php 4个表模糊查询union all,php – 单个mysql查询,用于选择25个记录(9 16),使用union all并基于2个不同的条件...-CSDN博客

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

这是我的查询……

(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)

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

尝试这样的事情：

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;