Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Oracle > Помогите исправить запрос.

Автор: ku78 25.4.2009, 21:36

Здравствуйте
Помогите исправить запрос. Есть три таблицы:
p_pribor в которой содержится информация о наименование прибора.
pover в которой отображается информация о поверки прибора.
p_spisok список приборов она связывает две преведущие таблицы.
Необходимо написать запрос что бы он выводил все приборы, а также выводил их последнее текущее состояние.

Текст запроса:

Код


SELECT 
p_pribor.nai as "Наименование", 
pover.naim as "Текущее состояние", 
pover.date_p as "Дата поверки", 
p_spisok.ID, 
p_spisok.id_p,
p_spisok.id_pov
FROM p_spisok
left join pover on p_spisok.id_pov = pover.ID   and POVER.DATE_P = 
(
select max(POVER.DATE_P)  from POVER where P_SPISOK.ID_POV = POVER.ID ) 

right join p_pribor on p_pribor.ID = p_spisok.id_p

Автор: Zloxa 25.4.2009, 21:58

Код


SELECT 
p_pribor.nai as "Наименование", 
pover.naim as "Текущее состояние", 
pover.date_p as "Дата поверки", 
p_spisok.ID, 
p_spisok.id_p,
p_spisok.id_pov
FROM p_pribor 
left join p_spisok on p_pribor.ID = p_spisok.id_p
left join (
  select max(naim) keep (dense rank last order by DATE_P nulls first) naim
           ,max(date_p)
           ,ID
  from pover
  group by ID 
) pover
  on p_spisok.id_pov = pover.ID

Автор: ku78 26.4.2009, 12:11

если так то возникает ошибка:

Код


SQL> SELECT 
  2  p_pribor.nai as "Наименование", 
  3  pover.naim as "Текущее состояние", 
  4  pover.date_p as "Дата поверки", 
  5  p_spisok.ID, 
  6  p_spisok.id_p,
  7  p_spisok.id_pov
  8  FROM p_pribor 
  9  left join p_spisok on p_pribor.ID = p_spisok.id_p
 10  left join (
 11    select max(naim) keep (dense rank last order by DATE_P nulls first) naim
 12             ,max(date_p)
 13             ,ID
 14    from pover
 15    group by ID 
 16  ) pover
 17    on p_spisok.id_pov = pover.ID 
 18  /
left join p_spisok on p_pribor.ID = p_spisok.id_p
                                    *
ошибка в строке 9:
ORA-02000: отсутствует ключевое слово DENSE_RANK


SQL>

Поисни вот эту строчу плиз?

Код


left join (
 11    select max(naim) keep (dense rank last order by DATE_P nulls first) naim
 12             ,max(date_p)
 13             ,ID
 14    from pover
 15    group by ID 
 16  ) pover
 17    on p_spisok.id_pov = pover.ID

И еше где можна прочитать про keep на русском?

Автор: ku78 26.4.2009, 14:01

Код


SQL> SELECT 
  2  p_pribor.nai as "Наименование", 
  3  pover.naim as "Текущее состояние", 
  4  pover.date_p as "Дата поверки", 
  5  p_spisok.ID, 
  6  p_spisok.id_p,
  7  p_spisok.id_pov
  8  FROM p_pribor 
  9  left join p_spisok on p_pribor.ID = p_spisok.id_p
 10  left join (
 11  select max(naim) keep (dense_rank last order by DATE_P nulls first) naim, max(date_p),ID from p
over group by ID 
 12  ) pover on p_spisok.id_pov = pover.ID 
 13  /
pover.date_p as "Дата поверки",
*
ошибка в строке 4:
ORA-00904: "POVER"."DATE_P": недопустимый идентификатор

в итоге получается что поле подругому называется

Добавлено @ 14:07
Вот так работает:

Код


SELECT 
p_pribor.nai as "Наименование", 
pover.naim as "Текущее состояние", 
pover.date_p as "Дата поверки", 
p_spisok.ID, 
p_spisok.id_p,
p_spisok.id_pov
FROM p_pribor 
left join p_spisok on p_pribor.ID = p_spisok.id_p
left join (
select max(naim) keep (dense_rank last order by DATE_P nulls first) naim, max(date_p)  date_p,ID from pover group by ID 
) pover on p_spisok.id_pov = pover.ID

спасибо за помощь)
но выводит серовно не так как нужно:

Наименование         Текущее состояние    Дата пов      ID        ID_P   ID_POV
--------------------            --------------------             --------              ---      -----      ---------
амперметр                       годен                25.04.09               1            1      1
амперметр            списание                   25.04.09                      2            1      2
вольтметр
комбинированый прибо
а мне нужна что бы выводились не все состояния а одно последнее ?
И еше где можна прочитать про keep  (dense_rank ) на русском?

Автор: Zloxa 26.4.2009, 19:10

Цитата(ku78 @ 26.4.2009, 14:01

)

И еше где можна прочитать про keep (dense_rank )

На русском не знаю, а почитать http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions058.htm#sthref1414

Автор: Zloxa 27.4.2009, 08:42

Код


SELECT 
max(p_pribor.nai) as "Наименование", 
max(pover.naim) keep (dense_rank last order by DATE_P nulls first) as "Текущее состояние", 
max(pover.date_p) as "Дата поверки", 
max(p_spisok.ID) id, 
max(p_spisok.id_p) id_p,
max(p_spisok.id_pov) id_pov
FROM p_pribor 
left join p_spisok on p_pribor.ID = p_spisok.id_p
left join pover on p_spisok.id_pov = pover.ID 
group by p_pribor.id

Только вот история умалчивает, что является ключем для p_pribor и в каких отношениях p_pribor и p_spisok.

Автор: ku78 28.4.2009, 12:28

Извените что не описал все нормальна.
Структура таблиц такая:

Код


------------------------------------------
CREATE TABLE P_PRIBOR
(
  ID   INTEGER      NOT NULL,
  NAI  VARCHAR2(20) NOT NULL
)
\
ALTER TABLE P_PRIBOR ADD PRIMARY KEY (ID)
------------------------------------------
CREATE TABLE P_SPISOK
(
  ID      INTEGER  NOT NULL,
  ID_P    INTEGER  NOT NULL,
  ID_POV  INTEGER  NOT NULL
)
\
ALTER TABLE P_SPISOK ADD PRIMARY KEY (ID)
------------------------------------------
CREATE TABLE POVER
(
  ID      INTEGER      NOT NULL,
  NAIM    VARCHAR2(20) NOT NULL,
  DATE_P  DATE         NOT NULL
)
ALTER TABLE POVER ADD PRIMARY KEY (ID)
------------------------------------------
ALTER TABLE P_SPISOK ADD (
  FOREIGN KEY (ID_POV) 
 REFERENCES POVER (ID),
  FOREIGN KEY (ID_P) 
 REFERENCES P_PRIBOR (ID));
-----------------------------------------

Автор: ku78 28.4.2009, 12:45

Спасибо за помощь вот этот вариант вроде работает)

Код


SELECT 
max(p_pribor.nai) as "Наименование", 
max(pover.naim) keep (dense_rank last order by DATE_P nulls first) as "Текущее состояние", 
max(pover.date_p) as "Дата поверки", 
max(p_spisok.ID) id, 
max(p_spisok.id_p) id_p,
max(p_spisok.id_pov) id_pov
FROM p_pribor 
left join p_spisok on p_pribor.ID = p_spisok.id_p
left join pover on p_spisok.id_pov = pover.ID 
group by p_pribor.id

Добавлено через 6 минут и 24 секунды
Толька я так и не понял нафига нужно keep (dense_rank...) применять, без него вроде тоже работает, выдает тоже самое что и с ним.

Код


SELECT 
max(p_pribor.nai) as "Наименование", 
max(pover.naim)  as "Текущее состояние", --keep (dense_rank last order by DATE_P nulls first)
max(pover.date_p) as "Дата поверки", 
max(p_spisok.ID) id, 
max(p_spisok.id_p) id_p,
max(p_spisok.id_pov) id_pov
FROM p_pribor 
left join p_spisok on p_pribor.ID = p_spisok.id_p
left join pover on p_spisok.id_pov = pover.ID 
group by p_pribor.id

Автор: Zloxa 28.4.2009, 15:26

Цитата(ku78 @ 28.4.2009, 12:45

)

, без него вроде тоже работает, выдает тоже самое что и с ним.

Это только кажется.

Код


SQL> with t as (select 1 ord, 1 val from dual
  2             union all select 2 ord, 4 val from dual
  3             union all select 3 ord, 2 val from dual
  4             union all select 3 ord, 3 val from dual)
  5  select
  6    max(val) keep(dense_rank last order by ord) max_keep
  7    , max(val)  max
  8    , min(val) keep(dense_rank last order by ord) min_keep
  9    , min(val) min
 10  from t
 11  ;
 
  MAX_KEEP        MAX   MIN_KEEP        MIN
---------- ---------- ---------- ----------
         3          4          2          1