Вывод записей с нулевыми значениями, даже если они отсутствуют

#sql #oracle #plsql

#sql #Oracle #plsql

Вопрос:

У меня есть таблица, как показано ниже в Oracle

gen_id serial_code is_verified
1 fmcg Y
1 smcg Y
1 xmcg N
2 smcg Y
2 fmcg Y
2 2mcg Y
3 smcg Y
3 amcg Y

Теперь мне нужен вывод для max gen_id, который в данном случае равен 3, и serial_code ‘smcg’ и ‘fmcg’. Я могу легко получить вывод с помощью запросов, но я хочу его в формате, как показано ниже.

gen_id serial_code is_verified
3 smcg Y
3 fmcg not_present

Как я могу этого добиться? Любая помощь приветствуется. Заранее спасибо

Комментарии:

1. Почему fmcg они должны быть возвращены в результирующий набор для gen_id=3 ? В чем логика?

Ответ №1:

PARTITION Для этого вы можете использовать OUTER JOIN ed.

Из Oracle 12:

 SELECT t.gen_id,
       s.serial_code,
       COALESCE(t.is_verified, 'not_present') AS is_verified
FROM   (SELECT 'smcg' AS serial_code FROM DUAL UNION ALL
        SELECT 'fmcg' FROM DUAL) s
       LEFT OUTER JOIN (
         SELECT *
         FROM   table_name
         ORDER BY gen_id DESC
         FETCH FIRST ROW WITH TIES
       ) t
       PARTITION BY (gen_id)
       ON (s.serial_code = t.serial_code)
 

В Oracle 11 вы можете использовать:

 SELECT t.gen_id,
       s.serial_code,
       COALESCE(t.is_verified, 'not_present') AS is_verified
FROM   (SELECT 'smcg' AS serial_code FROM DUAL UNION ALL
        SELECT 'fmcg' FROM DUAL) s
       LEFT OUTER JOIN (
         SELECT gen_id, serial_code, is_verified
         FROM   (
           SELECT t.*,
                  RANK() OVER (ORDER BY gen_id DESC) AS rnk
           FROM   table_name t
         )
         WHERE  rnk = 1
       ) t
       PARTITION BY (gen_id)
       ON (s.serial_code = t.serial_code)
 

Что для образца данных:

 CREATE TABLE table_name (gen_id, serial_code, is_verified) AS
SELECT 1, 'fmcg', 'Y' FROM DUAL UNION ALL
SELECT 1, 'smcg', 'Y' FROM DUAL UNION ALL
SELECT 1, 'xmcg', 'N' FROM DUAL UNION ALL
SELECT 2, 'smcg', 'Y' FROM DUAL UNION ALL
SELECT 2, 'fmcg', 'Y' FROM DUAL UNION ALL
SELECT 2, '2mcg', 'Y' FROM DUAL UNION ALL
SELECT 3, 'smcg', 'Y' FROM DUAL UNION ALL
SELECT 3, 'amcg', 'Y' FROM DUAL;
 

Оба вывода:

    PARTITION BY (gen_id)
   ON (s.serial_code = t.serial_code)
 
GEN_ID SERIAL_CODE IS_VERIFIED
3 fmcg not_present
3 smcg Y

Oracle 18 db<> скрипка здесь — Oracle 11 db<> скрипка здесь

Комментарии:

1. Это работает хорошо.. Спасибо!!!!

Ответ №2:

Это делает это, но выглядит не очень красиво. Чтение комментариев в коде.

 SQL> WITH
  2     test (gen_id, serial_code, is_verified)
  3     AS
  4     -- sample data
  5        (SELECT 1, 'fmcg', 'Y' FROM DUAL
  6         UNION ALL
  7         SELECT 1, 'smcg', 'Y' FROM DUAL
  8         UNION ALL
  9         SELECT 1, 'xmcg', 'N' FROM DUAL
 10         UNION ALL
 11         SELECT 3, 'smcg', 'Y' FROM DUAL
 12         UNION ALL
 13         SELECT 3, 'amcg', 'Y' FROM DUAL),
 

  14     maxgen (gen_id)
 15     AS
 16     -- MAX gen_id value; will be used later
 17     (SELECT MAX (gen_id) FROM test),
 18     temp
 19     AS
 20        -- compose NOT_PRESENT rows that don't exist in original (TEST) table
 21        (SELECT t.gen_id, c.serial_code, 'not_present' is_verified
 22           FROM test t
 23                CROSS JOIN (SELECT DISTINCT serial_code
 24                              FROM test) c
 25                JOIN maxgen m
 26                   ON     m.gen_id = t.gen_id
 27                      AND (t.gen_id, c.serial_code) NOT IN
 28                             (SELECT gen_id, serial_code FROM test))
 29  -- finally: union existing rows (from TEST) with NOT_PRESENT ones (from TEMP)
 30  SELECT a.gen_id, a.serial_code, a.is_verified
 31    FROM test a JOIN maxgen m ON m.gen_id = a.gen_id
 32   WHERE serial_code IN ('smcg', 'fmcg')
 33  UNION
 34  SELECT x.gen_id, x.serial_code, x.is_verified
 35    FROM temp x
 36   WHERE x.serial_code IN ('smcg', 'fmcg');

    GEN_ID SERIAL_CODE  IS_VERIFIED
---------- ------------ -----------
         3 fmcg         not_present
         3 smcg         Y

SQL>