theskiguy Ответов: 2

Запрос Oracle, который имеет другое предложение where для каждого столбца

У меня есть таблица Oracle со следующими строками:

Машина
OEEDate
Shift1
Шифт2
Shift3

Данные имеют вид, похожий на этот:
500, 8/1/2017, 0, 0, 1
500, 8/2/2017, 1, 1, 1

У меня в таблице есть данные за целый год. У меня также есть около 50 различных машин для каждого свидания. Я хотел бы сделать запрос, в котором я мог бы отобразить данные за целые недели (7 дней с понедельника по воскресенье) для конкретной машины, но данные должны отображаться в Столбцах, а не в нескольких строках. Вот что я ищу. Чтобы упростить ситуацию, я показываю данные только за 2 дня. Поскольку одно и то же имя столбца повторяется, мне нужно будет переименовать каждый столбец для каждого дня.

машина, oeedate-м Shift1-м, Шифт2-м, сдвиг 3-м oeedate-Т Shift1-Т Shift3-Т

500, 8/1/2017, 0, 0, 1, 8/2/17, 1, 1, 1

Что я уже пробовал:

Я думаю, что это было бы приложение для использования оператора case, но я не могу понять правильный синтаксис. Я использовал оператор Case только один раз, так что я довольно новичок в его использовании. Это то, что я пробовал, но это не работает:

select machine, oeedate, shift1, shift2, shift3,
(case when oeedate = TO_DATE('08/01/2017 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') then oeedate end) as shift1A,
(case when oeedate = TO_DATE('08/02/2017 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') then oeedate end) as shift1B
from plannedproductiontimes      
where machine = '500'


Если это имеет значение, то в данный момент мы все еще используем Oracle 9.

2 Ответов

Рейтинг:
16

RAMASWAMY EKAMBARAM

Поскольку вы говорите, что транспонирование (PIVOT)не работает в 9i, вы можете попробовать это вместо оператора CASE. Хотя есть подзапросы, это проще, чем использовать CASE в данном конкретном контексте. (Просто проверьте часть to_char(< date>, 'day') - что она возвращает день недели - я получил это в Google)

select mon.*, tue.*, wed.* .. sun.*
from
(
select machine, to_char(oeedate, 'mm/dd/yyyy') monday, shift1_mon, shift2_mon, shift3_mon
from plannedproductiontimes
where upper(substr(to_char(oeedate, 'day'), 1, 3)) = 'MON'
and machine = '500'
) mon,
(
select to_char(oeedate, 'mm/dd/yyyy') tuesday, shift1_tue, shift2_tue, shift3_tue
from plannedproductiontimes
where upper(substr(to_char(oeedate, 'day'), 1, 3)) = 'TUE'
and machine = '500'
) tue,
...
...
(
) sun
where to_date(tuesday, 'mm/dd/yyyy') = to_date(monday, 'mm/dd/yyyy') + 1
and to_date(wednesday, 'mm/dd/yyyy') = to_date(monday, 'mm/dd/yyyy') + 2
...
...
and to_date(sunday, 'mm/dd/yyyy') = to_date(monday, 'mm/dd/yyyy') + 6
order by machine, to_date(monday, 'mm/dd/yyyy')
;

theskiguy

Большое спасибо. Это очень близко к тому, что мне нужно. Я сделал несколько незначительных изменений. Я хотел показать только недельную работу данных, поэтому добавил еще один пункт в заключительное предложение WHERE. Кроме того, мне пришлось изменить синтаксис, связанный с аббревиатурами столбцов для столбцов сдвигов.

Моя единственная проблема, которая у меня сейчас есть, - это попытка выяснить, что делать, если я хочу отобразить данные за эту же неделю для более чем 1 машины за раз, отсортированных по машинам. Если я добавлю дополнительную машину в предложение WHERE, она создаст дубликаты результатов. Я думаю, что мог бы объединить результаты для каждой машины вместе, но с более чем 50 машинами я пытаюсь выяснить, есть ли лучший способ.

RAMASWAMY EKAMBARAM

"для более чем 1 машины" - я не упустил из виду это требование, но вы можете выполнить его самостоятельно. Вместо запроса конкретной машины ("where machine = '500'") вам придется выбрать машину для каждого из дней (в моем примере я сделал это только для "понедельника") и псевдоним как "mch_mon", "mch_tue".. и в условии для объединения подзапросов вам нужно будет сопоставить машины (mch_mon = mch_tue) в дополнение к совпадению дат.
Единственная трудность заключается в том, что вам нужно будет прописывать отдельные столбцы (псевдонимы) для каждого дня, а не просто писать пн.*, вт.*...!

Рейтинг:
1

GKP1992

Привет,

Я думаю PIVOT-ПРЕДЛОЖЕНИЕ PLSQL делает именно то, что вы пытаетесь сделать здесь.

Я предлагаю вам взглянуть и попробовать. Возможно, вам придется объединить результаты из ваших сводных таблиц, но это делает свое дело.

Обновление после вопроса:

Я думаю, что вы на правильном пути с вашим запросом, вам просто нужно сформировать лучший запрос.
Вы можете взять некоторые идеи из здесь (ручное вращение) или здесь.

Возможно, вам придется потратить некоторое время на то, чтобы понять, как объединить ваши результаты так, как вы хотите, тем не менее, это правильный путь.

Спасибо и удачи.

theskiguy

Извините, но я забыл упомянуть, что мы все еще работаем под управлением Oracle 9. Похоже, что эта команда требует Oracle 11. Я обновил свой вопрос, чтобы добавить требование Oracle 9.

jgakenhe

XMLAGG будет работать. Возможно, вы захотите посмотреть здесь пример по сравнению с PIVOT: http://www.dba-oracle.com/t_converting_rows_columns.htm

theskiguy

Из моих первоначальных тестов это выглядит так, как будто XMLAGG работает, но он помещает все значения в один столбец, разделенный запятыми. Мне действительно нужен отдельный столбец для каждого значения, чтобы я мог отображать его в своей таблице данных. Я что-то упустил?