如何在sql中生成单元格内两个数字之间的数字范围

qvsjd97n 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(427)

我有一张只有一列的table duration 包含整数值，我正在尝试使用sql查询构建另一列，该列将包含一个介于1和 duration 列。
例如：

duration | range
3        | [1, 2, 3]
3        | [1, 2, 3]
2        | [1, 2]
1        | [1]
...

我在js中找到了一个潜在的解决方案。

create or replace function list_range(DURATION double)
  returns VARCHAR
  language javascript
  strict
  as 'return [...Array(DURATION).keys()];';

SELECT 
   t.*,
   list_range(t.duration) as range
FROM table t

你觉得这个解决方案怎么样？可以优化吗？

sql snowflake-cloud-data-platform range snowsql

来源：https://stackoverflow.com/questions/63036853/how-to-generate-a-range-of-numbers-between-two-numbers-inside-a-cell-in-sql

1条答案

按热度按时间

yfwxisqw1#

with temp as (
    select distinct duration, level as l
    from duration
    connect by level <= duration)
select duration,
       '['||listagg(l, ', ') within group(order by l)||']' as range
from temp
group by duration
order by 1 desc;

赞(0）回复(0）举报 2021-07-24

我来回答

如何在sql中生成单元格内两个数字之间的数字范围

1条答案

相关问题

热门标签

最新问答