按组按顺序查找间隙
按组按顺序查找间隙
提问于 2019-10-03 14:35:40
我有不同城市和价值观的表格。我想按城市查找差距，所以我有表：
City Value
Helsinki 1
Helsinki 2
Helsinki 5
Kuopio 4
Kuopio 5
Joensuu 1
Joensuu 2
Joensuu 3我想要一张按城市划分的空白表
City Value
Helsinki 3
Helsinki 4
Kuopio 1
Kuopio 2
Kuopio 3
Joensuu 4
Joensuu 5我知道我可以使用conncet by
select rownum from dual connect by level<= 6 minus select value from table但我不知道，我如何才能做到这一点的小组。
回答 1
Stack Overflow用户
发布于 2019-10-03 14:54:49
您可以使用减号运算符来实现这一点--您最初的想法
SQL> with city as (
2 select 'Helsinki' as name, 1 as value from dual union all
3 select 'Helsinki' as name, 2 as value from dual union all
4 select 'Helsinki' as name, 5 as value from dual union all
5 select 'Kuopio' as name, 4 as value from dual union all
6 select 'Kuopio' as name, 5 as value from dual union all
7 select 'Joensuu' as name, 1 as value from dual union all
8 select 'Joensuu' as name, 2 as value from dual union all
9 select 'Joensuu' as name, 3 as value from dual
10 )
11 SELECT NAME, LVL
12 FROM (SELECT DISTINCT NAME FROM CITY)
13 JOIN (SELECT LEVEL AS LVL FROM DUAL
14 CONNECT BY LEVEL <= 5) ON ( 1 = 1 )
15 MINUS
16 SELECT NAME, VALUE
17 FROM CITY
18 ORDER BY 1, 2;
NAME LVL
-------- ----------
Helsinki 3
Helsinki 4
Joensuu 4
Joensuu 5
Kuopio 1
Kuopio 2
Kuopio 3
7 rows selected.
SQL>干杯！！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58213516
复制相关文章
相似问题
腾讯云开发者
