英文:
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 850003
850015 850017
这是一种选项，可以返回所需的结果（并避免**重复**）：
SQL> SELECT start_sn + COLUMN_VALUE - 1 AS sn
2 FROM number_ranges
3 CROSS JOIN
4 TABLE (
5 CAST (
6 MULTISET ( SELECT LEVEL
7 FROM DUAL
8 CONNECT BY LEVEL <= end_sn - start_sn + 1) AS SYS.odcinumberlist))
9 ORDER BY sn;
SN
----------
850000
850001
850002
850003
850015
850016
850017
7 rows selected.
SQL>;
英文:
Sample table:
SQL> SELECT * FROM number_ranges;
START_SN END_SN
---------- ----------
850000 850003
850015 850017
This is one option which returns desired result (and avoids duplicates):
SQL> SELECT start_sn + COLUMN_VALUE - 1 AS sn
2 FROM number_ranges
3 CROSS JOIN
4 TABLE (
5 CAST (
6 MULTISET ( SELECT LEVEL
7 FROM DUAL
8 CONNECT BY LEVEL <= end_sn - start_sn + 1) AS SYS.odcinumberlist))
9 ORDER BY sn;
SN
----------
850000
850001
850002
850003
850015
850016
850017
7 rows selected.
SQL>
答案2
得分: -1
要将一个数字范围在Oracle中拆分为单独的数字，您可以使用子查询、连接和CONNECT BY LEVEL子句等技巧的组合。以下是一个实现所需结果的示例查询：
WITH number_ranges AS (
SELECT 850000 AS start_sn, 850003 AS end_sn FROM dual
UNION ALL
SELECT 850015 AS start_sn, 850017 AS end_sn FROM dual
)
SELECT start_sn + LEVEL - 1 AS sn
FROM number_ranges
CONNECT 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 dual
UNION ALL
SELECT 850015 AS start_sn, 850017 AS end_sn FROM dual
)
SELECT start_sn + LEVEL - 1 AS sn
FROM number_ranges
CONNECT 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论