如果计划为 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屋!