如果计划为 1 年，则每天获取 3 行 [英] fetch 3 rows every day if scheduling for 1 year

问题描述

我有一个表格圣经，其中包含两列 id、chapter_name，其外观如下所示:-

i have one table bibles that having two columns id, chapter_name and its look like below:-

id     chapter_name  
365      chapter_1    
366      chapter_2
367      chapter_3
368      chapter_4
369      chapter_5
370      chapter_6
...       ....
1456    chater_1092

总记录 1092 和 id 将从 365 到 1465 开始固定.现在用户每天将阅读 3 章.假设计划从今天开始，即2020 年 2 月 23 日到2021 年 2 月 20 日.因此，用户要求在 2020 年 2 月 23 日发布 3 章，在 2020 年 2 月 24 日发布接下来的 3 章，依此类推.

Total Records 1092 and id will start form 365 to 1465 fixed. Now user will read 3 chapters every day. Suppose scheduling starts from today i.e is 23rd feb 2020 to 20th feb 2021. So user requests for 23 feb 2020 it will came 3 chapters and 24th feb 2020 it will came next 3 chapters and so on.

id     chapter_name        date
365      chapter_1       2020-02-23    
366      chapter_2       2020-02-23
367      chapter_3       2020-02-23
368      chapter_4       2020-02-24
369      chapter_5       2020-02-24
370      chapter_6       2020-02-24
...       ....
1454    chater_1090      2021-02-20
1455    chater_1091      2021-02-20
1456    chater_1092      2021-02-20

现在假设今天是 2020 年 2 月 24 日，用户将在应用中看到 chapter_4、chapter_5、chapter_6.注意:- 我不想在表中存储日期列.原因是我需要每年更新日期列.任何人都可以帮助我如何每天获取 3 条记录.所以我预计 2020 年 2 月 24 日的产出

So now suppose today is 24th feb 2020 user will see chapter_4, chapter_5, chapter_6 in the app. Note:- i dont want to store date column in the table. reason for that i need to update the date columns every year. Can anyone help me how can i fetch 3 records every day. So my expected output for 24th feb 2020

id     chapter_name        date
368      chapter_4       2020-02-24
369      chapter_5       2020-02-24
370      chapter_6       2020-02-24    

推荐答案

您可以使用 dense_rank() 和算术将行分成 3 组:

You can use dense_rank() and arithmetic to put the rows in groups of 3:

select b.*,
       ceiling(dense_rank() over (order by id) / 3)
from bibles b

问题是如何获取日期.根据您的示例，这可能是:

The question is then how to get the dates. Based on your example, this might be:

select b.*,
       '2020-02-23' + interval ( ceiling(dense_rank() over (order by id) / 3) - 1) day
from bibles b;

然而，-1 取决于结果集中的第一行是什么.如果你想让它从 365 开始，那么:

However, the - 1 depends on what the first row is in the the result set. If you want it to start at 365, then:

select b.*,
       '2020-02-23' + interval ( ceiling(dense_rank() over (order by id) / 3) - 1) day
from bibles b
where id >= 365;

这篇关于如果计划为 1 年，则每天获取 3 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！