Oracle SQL:组合列表的字符与值

在Oracle SQL中，我想创建一个视图。

在第1列中，它应该有一个不同的字符列表。

select distinct *name* from view1
--> name1
name2
name3
name4...

现在在第二列中，应该有数字，这些数字取决于这个数字:

select max(number) number_max from view2
--> 17

现在我希望第二列是升序的，这取决于那个数字。每个名字的数字列表。这个表应该是这样的:

name1 18  ->(number_max+1)
name1 19  ->(number_max+2)
name1 20
name2 18
name2 19
name2 20
name3 18
....

我该怎么做?非常感谢!

我是这样理解的:

示例数据(您的视图，view1和view2，简化):

SQL> with
2  view1 (name) as
3    (select 'name1' from dual union all
4     select 'name1' from dual union all
5     select 'name1' from dual union all
6     select 'name2' from dual union all
7     select 'name3' from dual union all
8     select 'name3' from dual
9    ),
10  view2 (c_number) as
11    (select 15 from dual union all
12     select 17 from dual
13    )
14  --

查询从这里开始;交叉连接到view1的子查询仅获取最大列数值，然后将其添加到row_number分析函数的结果中，该分析函数将每个name划分数据:

15  select
16    a.name as col1,
17    row_number() over (partition by a.name order by null) + b.number_max as col2
18  from view1 a cross join (select max(c_number) number_max from view2) b
19  order by 1, 2;
COL1        COL2
----- ----------
name1         18
name1         19
name1         20
name2         18
name3         18
name3         19
6 rows selected.
SQL>

您可以使用GROUP BY name来查找DISTINCT名称，并通过在MAX分析函数中嵌套MAX聚合函数来查找最大最大值，然后将CROSS JOIN嵌套到行生成器中。

例如，如果每个name需要3行:

SELECT m.name,
m.number_max + l.value AS value
FROM   ( SELECT name,
MAX(MAX("NUMBER")) OVER () AS number_max
FROM   table_name
GROUP BY name
) m
CROSS JOIN (
SELECT LEVEL AS value FROM DUAL CONNECT BY LEVEL <= 3
) l;

如果你有样本数据:

CREATE TABLE table_name (name, "NUMBER") AS
SELECT 'name1', 17 FROM DUAL UNION ALL
SELECT 'name1',  1 FROM DUAL UNION ALL
SELECT 'name1',  7 FROM DUAL UNION ALL
SELECT 'name2', 15 FROM DUAL UNION ALL
SELECT 'name2', 15 FROM DUAL UNION ALL
SELECT 'name3',  5 FROM DUAL UNION ALL
SELECT 'name3',  7 FROM DUAL UNION ALL
SELECT 'name4',  2 FROM DUAL UNION ALL
SELECT 'name4',  1 FROM DUAL;

输出:

相关内容

最新更新

热门标签：