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

1 回答

• 1

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

  尝试这样的事情：

  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;
  

  回复于 2024-04-23T21:35:46+08:00