mysql - MYSQL如何显示最近两天最受欢迎的商品

我试着展示最近两天最受欢迎的项目，但是这个视图允许输入两天前发生的项目。
它是为了找到最近两天最受欢迎的（可能20-30个项目）并用随机项目填充剩余的项目（需要1000个项目在任何时候都可以查看）
我该怎么解决？
谢谢你

CREATE
    ALGORITHM = UNDEFINED
    DEFINER = `XX`@`XX`
    SQL SECURITY DEFINER
VIEW `trending` AS
    select
        `question`.`name` AS `name`,
        `question`.`questionUrl` AS `questionUrl`,
        `question`.`miRating` AS `miRating`,
        `question`.`imageUrl` AS `imageUrl`,
        `question`.`miThumbnail` AS `miThumbnail`,
        `question`.`foundOn` AS `foundOn`,
        `question`.`myId` AS `myId`
    from
        (`question`
        join `feed` ON ((`question`.`myId` = `feed`.`question_id`)))
    group by `question`.`name`
    order by (`feed`.`timeStamp` >= (now() - interval 1 day)) desc ,
    (`feed`.`question_id` is not null) desc ,
    (((`question`.`likesCount` * 0.8) + (`question`.`commentsCount` * 0.6)) + ((`question`.`sharesCount` * 1) / 2.4)) desc
    limit 0 , 1000

最佳答案

问题是您是按question_name分组的，但是查询中有很多其他列，包括select和order by中的列。MySQL为它们选择任意值。解决这一问题的一种方法是只使用order by子句中的最大时间条件：

select q.`name` AS `name`, q.`questionUrl` AS `questionUrl`, q.`miRating` AS `miRating`,
       q.`imageUrl` AS `imageUrl`, q.`miThumbnail` AS `miThumbnail`,
       q.`foundOn` AS `foundOn`, q.`myId` AS `myId`
from `question` q join
     `feed` f
      ON q.`myId` = f.`question_id`
group by q.`name`
order by (max(f.`timeStamp`) >= (now() - interval 1 day)) desc ,
         (f.`question_id` is not null) desc ,
         (((q.`likesCount` * 0.8) + (q.`commentsCount` * 0.6)) + ((q.`sharesCount` * 1) / 2.4)) desc
limit 0 , 1000

关于mysql - MYSQL如何显示最近两天最受欢迎的商品，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18814448/