MYSQL如何显示最近两天 HitTest 门的项目

Question

我试图显示过去两天最流行的项目，但这个 View 让两天前发生的项目出现了。

它是为了找到最近两天最流行的(可能是 20-30 个项目)并用随机项目填充剩余的( View 上始终需要 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

Answer 1

问题是您按 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