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

Откуда: Москва
Сообщений: 67
Доброго времени суток!

Помогите, пожалуйста, написать следующий запрос.

Есть две таблицы:

CREDIT
uid1 CREDIT_TYPE CREDIT_SUM date_close
1 qwe 10000 27.08.14
1 qwe 500 27.08.14
1 qwe 200 27.08.14
1 qwe 100 25.08.14
1 qwe 100 27.08.14


и card_credit

uid1 CREDIT_TYPE CREDIT_SUM date_close
1 444 10000 27.08.14
1 444 500 27.08.14


Из них нужно выбрать 3 записи по следующим условиям:
- если в CREDIT количество записей больше или равно 3, то отбираем 2 записи из CREDIT с максимальным значением суммы и даты. Добавляем к выборке одну запись из card_credit также с максимальной суммой и датой

Ожидаемый результат для описанных выше таблиц
uid1 CREDIT_TYPE CREDIT_SUM date_close
1 qwe 10000 27.08.14
1 qwe 500 27.08.14
1 444 10000 27.08.14


Пока получилось только объединить таблицы и отсортировать каждую из таблиц по сумме и дате, как можно выбрать именно 3 требуемые строки?

with CREDIT as (
select 1 as uid1,  'qwe' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('25-08-14', 'dd-mm-yy') as date_close from dual
union all
select  1 as uid1,  'qwe' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual
union all
select  1 as uid1, 'qwe' as CREDIT_TYPE, 200 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual
union all
select  1 as uid1, 'qwe' as CREDIT_TYPE, 10000 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual
union all
select  1 as uid1, 'qwe' as CREDIT_TYPE, 500 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual
), 
card_credit as (
select 1 as uid1,  '444' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('25-08-14', 'dd-mm-yy') as date_close from dual
union all
select  1 as uid1,  '444' as CREDIT_TYPE, 300 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual
)
select  *
    FROM (
  SELECT flg, uid1,
    CREDIT_TYPE,
    CREDIT_SUM,
    date_close,
    row_number() over (partition BY uid1 ORDER BY sumb, date_close) srlno
  FROM
    (SELECT 
    1 as flg,
    uid1,
    CREDIT_TYPE,
    CREDIT_SUM,
    date_close ,
      dense_rank() over(partition BY uid1 ORDER BY CREDIT_SUM DESC) sumb
    FROM CREDIT
    )
union all 
   SELECT flg, uid1,
    CREDIT_TYPE,
    CREDIT_SUM,
    date_close,
    row_number() over (partition BY uid1 ORDER BY sumb, date_close) srlno
  FROM
    (SELECT 
    2 as flg,
    uid1,
    CREDIT_TYPE,
    CREDIT_SUM,
    date_close ,
      dense_rank() over(partition BY uid1 ORDER BY CREDIT_SUM DESC) sumb
    FROM card_credit
    )
)
;
14 окт 14, 18:08    [16703554]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
AmKad
Member

Откуда:
Сообщений: 5222
mmm-crash,

with CREDIT as (
select 1 as uid1,  'qwe' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('25-08-14', 'dd-mm-yy') as date_close from dual -- where 1=0
union all
select  1 as uid1,  'qwe' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual -- where 1=0
union all
select  1 as uid1, 'qwe' as CREDIT_TYPE, 200 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual -- where 1=0
union all
select  1 as uid1, 'qwe' as CREDIT_TYPE, 200 as CREDIT_SUM, to_date('28-08-14', 'dd-mm-yy') as date_close from dual -- where 1=0
), 
card_credit as (
select 1 as uid1,  '444' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('25-08-14', 'dd-mm-yy') as date_close from dual -- where 1=0
union all
select  1 as uid1,  '444' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual -- where 1=0
union all
select  1 as uid1,  '444' as CREDIT_TYPE, 100 as CREDIT_SUM, to_date('27-08-14', 'dd-mm-yy') as date_close from dual --  where 1=0
)

select *
from
 (select t.*,
  row_number() over (partition by uid1, credit_type order by CREDIT_SUM desc, date_close desc) rn,
  count    (decode(credit_type, 'qwe', 1)) over (partition by uid1) cnt_cred_qwe,
  count    (decode(credit_type, '444', 1)) over (partition by uid1) cnt_cred_444
  from
    (select * from CREDIT union all
     select * from card_credit
    ) t
 )
where rn <= decode(credit_type, 'qwe', 3-sign(cnt_cred_444), 3 - least(cnt_cred_qwe, 3-sign(cnt_cred_444)));
14 окт 14, 18:50    [16703770]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
mmm-crash
Member

Откуда: Москва
Сообщений: 67
AmKad,

спасибо большое!
14 окт 14, 19:25    [16703937]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить