問題

我有不同城市和價值的表.我想按城市找到差距,所以我有表:

 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
 

我知道我可以用connect

 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 – Fiddle here

  相同標籤的其他問題

oracle
下一個問題: