得到两个数字之间的偶数/奇数/所有数
得到两个数字之间的偶数/奇数/所有数
提问于 2015-08-20 06:39:40
我希望在一个(或两个)列中显示两个数字(1-9；2-10；11-20)之间的所有数字(偶数/奇数/混合)。
示例初始数据：
| rang | | r1 | r2 |
-------- -----|-----
| 1-9 | | 1 | 9 |
| 2-10 | | 2 | 10 |
| 11-20 | or | 11 | 20 |
CREATE TABLE initialtableone(rang TEXT);
INSERT INTO initialtableone(rang) VALUES
('1-9'),
('2-10'),
('11-20');
CREATE TABLE initialtabletwo(r1 NUMERIC, r2 NUMERIC);
INSERT INTO initialtabletwo(r1, r2) VALUES
('1', '9'),
('2', '10'),
('11', '20');结果：
| output |
----------------------------------
| 1,3,5,7,9 |
| 2,4,6,8,10 |
| 11,12,13,14,15,16,17,18,19,20 |回答 1
Stack Overflow用户
回答已采纳
发布于 2015-08-20 07:01:40
就像这样：
create table ranges (range varchar);
insert into ranges
values
('1-9'),
('2-10'),
('11-20');
with bounds as (
select row_number() over (order by range) as rn,
range,
(regexp_split_to_array(range,'-'))[1]::int as start_value,
(regexp_split_to_array(range,'-'))[2]::int as end_value
from ranges
)
select rn, range, string_agg(i::text, ',' order by i.ordinality)
from bounds b
cross join lateral generate_series(b.start_value, b.end_value) with ordinality i
group by rn, range这一产出如下：
rn | range | string_agg
---+-------+------------------------------
3 | 2-10 | 2,3,4,5,6,7,8,9,10
1 | 1-9 | 1,2,3,4,5,6,7,8,9
2 | 11-20 | 11,12,13,14,15,16,17,18,19,20页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32112061
复制相关文章
相似问题
腾讯云开发者
