Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 помогите с запросом  [new]
YOleg
Member

Откуда:
Сообщений: 76
Доброго времени суток!
Вопрос на составление запроса.
Есть 2 таблицы t1 и t2

WITH t1 AS
     (SELECT 1 AS "D"
        FROM DUAL
      UNION ALL
      SELECT 1
        FROM DUAL
      UNION ALL
      SELECT 2
        FROM DUAL
      UNION ALL
      SELECT 2
        FROM DUAL
      UNION ALL
      SELECT 3
        FROM DUAL),
     t2 AS
     (SELECT 1 AS "D"
        FROM DUAL
      UNION ALL
      SELECT 2
        FROM DUAL)
SELECT *
  FROM t1, t2
 WHERE t1.d = t2.d


Каким образом написать запрос, чтобы выполнялись следующие условия:
1. Если в t2 есть записи, которые присутствуют в t1, то выводить в запрос только их.
2. Если в t2 нет записей, то выводить полностью таблицу t1.

Заранее благодарен!
25 ноя 12, 17:14    [13525605]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
YOleg
Доброго времени суток!
1. Если в t2 есть записи, которые присутствуют в t1, то выводить в запрос только их.


их это записи t2 или записи t1? Буду считать t1.

YOleg
Доброго времени суток!
2. Если в t2 нет записей, то выводить полностью таблицу t1.


Нет записей вобще или которые присутствуют в t1? Буду считать которые присутствуют в t1:

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 1 AS "D" FROM DUAL UNION ALL
 10              SELECT 2 FROM DUAL
 11             )
 12   SELECT  *
 13     FROM  t1
 14     WHERE t1.d IN (
 15                    SELECT  *
 16                      FROM  t2
 17                   )
 18  UNION ALL
 19    SELECT  *
 20      FROM  t1
 21      WHERE 0 = (
 22                 SELECT  COUNT(*)
 23                   FROM  t1,
 24                         t2
 25                   WHERE t1.d = t2.d
 26                )
 27  /

         D
----------
         1
         1
         2
         2

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 10 AS "D" FROM DUAL UNION ALL
 10              SELECT 20 FROM DUAL
 11             )
 12   SELECT  *
 13     FROM  t1
 14     WHERE t1.d IN (
 15                    SELECT  *
 16                      FROM  t2
 17                   )
 18  UNION ALL
 19    SELECT  *
 20      FROM  t1
 21      WHERE 0 = (
 22                 SELECT  COUNT(*)
 23                   FROM  t1,
 24                         t2
 25                   WHERE t1.d = t2.d
 26                )
 27  /

         D
----------
         1
         1
         2
         2
         3

SQL> 


SY.

Сообщение было отредактировано: 25 ноя 12, 18:07
25 ноя 12, 18:06    [13525779]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
Так будет лучше:

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 1 AS "D" FROM DUAL UNION ALL
 10              SELECT 2 FROM DUAL
 11             ),
 12       t3 AS (
 13               SELECT  *
 14                 FROM  t1
 15                 WHERE t1.d IN (
 16                                SELECT  *
 17                                  FROM  t2
 18                               )
 19             )
 20   SELECT  *
 21     FROM  t3
 22  UNION ALL
 23    SELECT  *
 24      FROM  t1
 25      WHERE 0 = (
 26                 SELECT  COUNT(*)
 27                   FROM  t3
 28                   WHERE ROWNUM = 1
 29                )
 30  /

         D
----------
         1
         1
         2
         2

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 10 AS "D" FROM DUAL UNION ALL
 10              SELECT 20 FROM DUAL
 11             ),
 12       t3 AS (
 13               SELECT  *
 14                 FROM  t1
 15                 WHERE t1.d IN (
 16                                SELECT  *
 17                                  FROM  t2
 18                               )
 19             )
 20   SELECT  *
 21     FROM  t3
 22  UNION ALL
 23    SELECT  *
 24      FROM  t1
 25      WHERE 0 = (
 26                 SELECT  COUNT(*)
 27                   FROM  t3
 28                   WHERE ROWNUM = 1
 29                )
 30  /

         D
----------
         1
         1
         2
         2
         3

SQL> 


SY.
25 ноя 12, 18:12    [13525799]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
YOleg
Member

Откуда:
Сообщений: 76
Спасибо Вам за ответ!

автор
их это записи t2 или записи t1? Буду считать t1.

Да, конечно же записей в таблице t1.

автор
Нет записей вобще или которые присутствуют в t1?

Нет записей вообще - таблица пуста.
25 ноя 12, 18:14    [13525803]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
YOleg
Нет записей вообще - таблица пуста.


Предполагая t1.D IS NOT NULL:

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 1 AS "D" FROM DUAL UNION ALL
 10              SELECT 2 FROM DUAL
 11             )
 12  SELECT  *
 13    FROM  t1
 14    WHERE d IN (
 15                SELECT  NVL(t2.d,t1.d)
 16                  FROM      t2
 17                        RIGHT JOIN
 18                            dual
 19                          ON 1 = 1
 20               )
 21  /

         D
----------
         1
         1
         2
         2

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 10 AS "D" FROM DUAL UNION ALL
 10              SELECT 20 FROM DUAL
 11             )
 12  SELECT  *
 13    FROM  t1
 14    WHERE d IN (
 15                SELECT  NVL(t2.d,t1.d)
 16                  FROM      t2
 17                        RIGHT JOIN
 18                            dual
 19                          ON 1 = 1
 20               )
 21  /

no rows selected

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 1 AS "D" FROM DUAL WHERE 1 = 2
 10             )
 11  SELECT  *
 12    FROM  t1
 13    WHERE d IN (
 14                SELECT  NVL(t2.d,t1.d)
 15                  FROM      t2
 16                        RIGHT JOIN
 17                            dual
 18                          ON 1 = 1
 19               )
 20  /

         D
----------
         1
         1
         2
         2
         3

SQL> 


SY.

Сообщение было отредактировано: 25 ноя 12, 18:24
25 ноя 12, 18:22    [13525816]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
SY
Предполагая t1.D IS NOT NULL


Ну а если t1.D NULLable да и проще:

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 1 AS "D" FROM DUAL UNION ALL
 10              SELECT 2 FROM DUAL
 11             )
 12  SELECT  *
 13    FROM  t1
 14    WHERE t1.d IN (
 15                   SELECT  *
 16                     FROM  t2
 17                  )
 18    OR 0 = (
 19            SELECT  COUNT(*)
 20              FROM  t2
 21              WHERE ROWNUM = 1
 22           )
 23  /

         D
----------
         1
         1
         2
         2

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 10 AS "D" FROM DUAL UNION ALL
 10              SELECT 20 FROM DUAL
 11             )
 12  SELECT  *
 13    FROM  t1
 14    WHERE t1.d IN (
 15                   SELECT  *
 16                     FROM  t2
 17                  )
 18    OR 0 = (
 19            SELECT  COUNT(*)
 20              FROM  t2
 21              WHERE ROWNUM = 1
 22           )
 23  /

no rows selected

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT 3 FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 1 AS "D" FROM DUAL WHERE 1 = 2
 10             )
 11  SELECT  *
 12    FROM  t1
 13    WHERE t1.d IN (
 14                   SELECT  *
 15                     FROM  t2
 16                  )
 17    OR 0 = (
 18            SELECT  COUNT(*)
 19              FROM  t2
 20              WHERE ROWNUM = 1
 21           )
 22  /

         D
----------
         1
         1
         2
         2
         3

SQL> 


SY.

Сообщение было отредактировано: 25 ноя 12, 18:32
25 ноя 12, 18:30    [13525832]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
SY
Ну а если t1.D NULLable да и проще
Так прозрачнее, а вот если данные такие, то null не выводится.
WITH t1 AS (
            SELECT 1 AS "D" FROM DUAL UNION ALL
            SELECT 1 FROM DUAL UNION ALL
            SELECT 2 FROM DUAL UNION ALL
            SELECT 2 FROM DUAL UNION ALL
            SELECT null FROM DUAL
           ),
     t2 AS (
            SELECT 10 AS "D" FROM DUAL UNION ALL
            SELECT null FROM DUAL
           )
И все же, думаю, лучше обойтись одним проходом по t2 если это возможно (предполагается что t2 создана и наличие строк можно проверять по rowid):
select *
  from t1
 where nvl(t1.d, 1e125) in
       (select nvl2(t2.rowid, nvl(t2.d, 1e125), nvl(t1.d, 1e125))
          from dual
          left join t2
            on 1 = 1)
Другое дело, что не очень нравится, что синтаксис не совершенен и вместо какого-нибудь cross left join приходится дописывать 1 = 1.
Правда с оракловым диалектом еще менее красиво:
select *
  from t1
 where nvl(t1.d, 1e125) in
       (select nvl2(t2.rowid, nvl(t2.d, 1e125), nvl(t1.d, 1e125))
          from dual, t2
         where nvl2(dummy, 1, 1) = nvl2(t2.d(+), 1, 1))
Симпатично выглядит с lateral:
SQL> create table t2 as SELECT 1 AS "D" FROM DUAL UNION ALL SELECT null FROM DUAL;

Table created.

SQL>
SQL> WITH t1 AS (
  2         SELECT 1 AS "D" FROM DUAL UNION ALL
  3         SELECT 1 FROM DUAL UNION ALL
  4         SELECT 2 FROM DUAL UNION ALL
  5         SELECT 2 FROM DUAL UNION ALL
  6         SELECT null FROM DUAL
  7        )
  8  select *
  9    from t1
 10   where nvl(t1.d, 1e125) in
 11         (select nvl2(t2.rowid, nvl(t2.d, 1e125), nvl(t1.d, 1e125))
 12            from lateral (select * from t2)(+) t2, dual);

         D
----------
         1
         1


3 rows selected.
+ Но как любая недокументированная штука, работает через раз. :)
SQL> select * from dual d2, lateral(select dual.dummy column_value from dual where 1 = 0)(+) d1;

D COLUMN_VALUE
- ---------------
X

1 row selected.

SQL> select * from lateral(select dual.dummy column_value from dual where 1 = 0)(+) d1, dual d2;

no rows selected

SQL> select * from dual d2, table(select cast(collect(dummy) as sys.odcivarchar2list) from dual where 1 = 0)(+) d1;

D COLUMN_VALUE
- ---------------
X

1 row selected.

SQL> select * from table(select cast(collect(dummy) as sys.odcivarchar2list) from dual where 1 = 0)(+) d1, dual d2;

COLUMN_VALUE    D
--------------- -
                X

1 row selected.
25 ноя 12, 20:32    [13526256]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
YOleg,

Таблицы идентичны?
25 ноя 12, 23:31    [13526942]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
dbms_photoshop
Так прозрачнее, а вот если данные такие, то null не выводится.


Не понял. С каких пор NULL стал равным NULL? Но если это входит в условие, то:

SQL> WITH t1 AS (
  2              SELECT 1 AS "D" FROM DUAL UNION ALL
  3              SELECT 1 FROM DUAL UNION ALL
  4              SELECT 2 FROM DUAL UNION ALL
  5              SELECT 2 FROM DUAL UNION ALL
  6              SELECT null FROM DUAL
  7             ),
  8       t2 AS (
  9              SELECT 10 AS "D" FROM DUAL UNION ALL
 10              SELECT null FROM DUAL
 11             )
 12  SELECT  *
 13    FROM  t1
 14    WHERE nvl(t1.d,binary_float_nan) IN (
 15                                         SELECT  nvl(t2.d,binary_float_nan)
 16                                           FROM  t2
 17                                        )
 18    OR 0 = (
 19            SELECT  COUNT(*)
 20              FROM  t2
 21              WHERE ROWNUM = 1
 22           )
 23  /

         D
----------


SQL>


SY.

Сообщение было отредактировано: 25 ноя 12, 23:59
25 ноя 12, 23:58    [13527042]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
SY
Не понял. С каких пор NULL стал равным NULL? Но если это входит в условие, то
Автору виднее, но как я понял, если null содержится в t2 то должeн попасть в результат и null из t1 согласно:
YOleg
Если в t2 есть записи, которые присутствуют в t1, то выводить в запрос только их
26 ноя 12, 02:37    [13527267]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
inFik
Member

Откуда:
Сообщений: 140
От скуки тоже набросал...
with t1 as
     (select 1 as d from dual union all
      select 1 from dual union all
      select 2 from dual union all
      select 2 from dual union all
      select 3 from dual),
     t2 as
     (select 1 as d from dual union all
      select 2 from dual)
select * from 
(      
select case when count(t2.d) over () = 0
        then t1.d
        else decode(row_number() over (partition by t1.d order by t1.d),1,t2.d,null)
        end d
  from t1, t2
 where t1.d = t2.d(+)
) where d is not null
26 ноя 12, 12:36    [13528686]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
inFik,

Дешевле, пожалуй, сделать distinct t2 чем после соединения row_number.
26 ноя 12, 13:13    [13529027]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить