MYSQL View 不填空点补1000

Question

以下查询从过去 2 天内提出的问题中获取热门问题。它查看提要表以了解最新讨论的内容，然后搜索标签表以查找其中哪一个最受欢迎。

我只得到大约 60 个结果，这很好，但我需要 1000 个结果。这意味着我需要用随机问题来填补其余部分。

我的 SQL 查询尝试执行此操作，但不会使用 Feed 表中没有的更多问题来填充 View 的其余部分。

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `popular` AS
    select 
        `q`.`name` AS `name`,
        `q`.`questionUrl` AS `questionUrl`,
        `q`.`miRating` AS `miRating`,
        `q`.`imageUrl` AS `imageUrl`,
        `q`.`foundOn` AS `foundOn`,
        `q`.`myId` AS `myId`
    from
        (`question` `q`
        join `feed` `f` ON ((`q`.`myId` = `f`.`question_id`))

        join `tag` `t` ON ((`q`.`myId` = `t`.`question_id`)))

    where
        (`t`.`name` like '%popular%')
    group by `q`.`name`
    order by (max(`f`.`timeStamp`) >= (now() - interval 1 day)) desc , (`q`.`myId` is not null) desc
    limit 0 , 1000comment

Answer 1

如果您需要随机问题，请删除 where 子句并将逻辑移至 order by:

select 
    `q`.`name` AS `name`,
    `q`.`questionUrl` AS `questionUrl`,
    `q`.`miRating` AS `miRating`,
    `q`.`imageUrl` AS `imageUrl`,
    `q`.`foundOn` AS `foundOn`,
    `q`.`myId` AS `myId`
from
    (`question` `q`
    join `feed` `f` ON ((`q`.`myId` = `f`.`question_id`))
    join `tag` `t` ON ((`q`.`myId` = `t`.`question_id`)))
group by `q`.`name`
order by (max(`f`.`timeStamp`) >= (now() - interval 1 day)) desc ,
         max(`t`.`name` like '%popular%') desc,
         rand()
limit 0 , 1000;