英文:
How to break number range to individual numbers oracle
问题
如何将数字范围拆分为单独的数字 Oracle
我的表中有以下数字范围
| START_SN | END_SN |
|---|---|
| 850000 | 850003 |
| 850015 | 850017 |
并且需要以下结果
| SN |
|---|
| 850000 |
| 850001 |
| 850002 |
| 850003 |
| 850015 |
| 850016 |
| 850017 |
英文:
How to break number range to individual numbers oracle
I have following number ranges in my table
| START_SN | END_SN |
|---|---|
| 850000 | 850003 |
| 850015 | 850017 |
And need following result.
| SN |
|---|
| 850000 |
| 850001 |
| 850002 |
| 850003 |
| 850015 |
| 850016 |
| 850017 |
答案1
得分: 0
以下是已翻译的部分：
样本表格：SQL> SELECT * FROM number_ranges;START_SN END_SN---------- ----------850000 850003850015 850017这是一种选项，可以返回所需的结果（并避免**重复**）：SQL> SELECT start_sn + COLUMN_VALUE - 1 AS sn2 FROM number_ranges3 CROSS JOIN4 TABLE (5 CAST (6 MULTISET ( SELECT LEVEL7 FROM DUAL8 CONNECT BY LEVEL <= end_sn - start_sn + 1) AS SYS.odcinumberlist))9 ORDER BY sn;SN----------8500008500018500028500038500158500168500177 rows selected.SQL>;
英文:
Sample table:
SQL> SELECT * FROM number_ranges;START_SN END_SN---------- ----------850000 850003850015 850017
This is one option which returns desired result (and avoids duplicates):
SQL> SELECT start_sn + COLUMN_VALUE - 1 AS sn2 FROM number_ranges3 CROSS JOIN4 TABLE (5 CAST (6 MULTISET ( SELECT LEVEL7 FROM DUAL8 CONNECT BY LEVEL <= end_sn - start_sn + 1) AS SYS.odcinumberlist))9 ORDER BY sn;SN----------8500008500018500028500038500158500168500177 rows selected.SQL>
答案2
得分: -1
要将一个数字范围在Oracle中拆分为单独的数字，您可以使用子查询、连接和CONNECT BY LEVEL子句等技巧的组合。以下是一个实现所需结果的示例查询：
WITH number_ranges AS (SELECT 850000 AS start_sn, 850003 AS end_sn FROM dualUNION ALLSELECT 850015 AS start_sn, 850017 AS end_sn FROM dual)SELECT start_sn + LEVEL - 1 AS snFROM number_rangesCONNECT BY LEVEL <= (end_sn - start_sn + 1)ORDER BY sn;
修改：
number_ranges 子查询用于表示您的表数据。您可以将其替换为您实际的表名。
英文:
To break a number range into individual numbers in Oracle, you can use a combination of techniques such as subqueries, joins, and the CONNECT BY LEVEL clause. Here's an example query that achieves the desired result:
WITH number_ranges AS (SELECT 850000 AS start_sn, 850003 AS end_sn FROM dualUNION ALLSELECT 850015 AS start_sn, 850017 AS end_sn FROM dual)SELECT start_sn + LEVEL - 1 AS snFROM number_rangesCONNECT BY LEVEL <= (end_sn - start_sn + 1)ORDER BY sn;
Modifications:
The number_ranges subquery is used to represent your table data. You can replace it with your actual table name.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论