問題

私はさまざまな都市と値があるテーブルを持っています。私は都市によってギャップを見つけたいので、私はテーブルを持っています:

 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を使うことができることを知っています

 select  rownum from dual connect by level<= 6 minus select value from table
 

しかし、私はグループでこれをどうやって行うことができるのか分かりません。

  ベストアンサー

パーティション化された外部結合を使用することができます。これは、self-joinとCROSS JOINを使用するよりも効率的です。

Oracleセットアップ:

 CREATE TABLE table_name ( City, Value ) AS
SELECT 'Helsinki', 1 FROM DUAL UNION ALL
SELECT 'Helsinki', 2 FROM DUAL UNION ALL
SELECT 'Helsinki', 5 FROM DUAL UNION ALL
SELECT 'Kuopio',   4 FROM DUAL UNION ALL
SELECT 'Kuopio',   5 FROM DUAL UNION ALL
SELECT 'Joensuu',  1 FROM DUAL UNION ALL
SELECT 'Joensuu',  2 FROM DUAL UNION ALL
SELECT 'Joensuu',  3 FROM DUAL
 

クエリ:

 SELECT city,
       v.value
FROM   ( SELECT LEVEL AS value
         FROM   DUAL
         CONNECT BY LEVEL <= ( SELECT MAX( value ) FROM table_name )
                             -- or just 5 if you want a static value
       ) v
       LEFT OUTER JOIN table_name t
       PARTITION BY ( t.City )
       ON ( v.value = t.value )
WHERE  t.value IS NULL
 

出力:

CITY     | VALUE
:------- | ----:
Helsinki |     3
Helsinki |     4
Joensuu  |     4
Joensuu  |     5
Kuopio   |     1
Kuopio   |     2
Kuopio   |     3

DB <>フィドルここで

  同じタグがついた質問を見る

oracle