每个列具有不同where子句的Oracle查询 [英] Oracle query that has a different where clause for each column

问题描述

我有一个包含以下行的Oracle表：



机器

OEEDate

Shift1

Shift2

Shift3



该数据看起来与此类似：

500 ，8/1 / 2017,0,0,1，

500,8 / 2 / 2,1,1,1



我表中有一整年的数据。每个日期我也有大约50台不同的机器。我想查询一下我可以为特定计算机显示一整周的数据（周一至周日7天），但数据显示在列中而不是多行。这是我正在寻找的。为简化起见，我只显示了2天的数据。由于重复相同的列名，我需要每天重命名每列。



machine，oeedate-M，Shift1-M，Shift2-M， Shift 3-M，oeedate-T，Shift1-T，Shift3-T



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



我尝试过：



我认为这将是一个使用case语句的应用程序，但我无法弄清楚正确的语法。我之前只使用过一次Case语句，所以我很习惯使用它。这是我尝试过的但是没有成功：

选择机器，oeedate，shift1，shift2，shift3，
 （oeedate = TO_DATE（'08 / 01/2017：00：00 AM'，'MM / DD / YYYY HH：MI：SS AM'）然后结束）作为shift1A，
（oededate的情况） = TO_DATE（'08 / 02/2017：00：00 AM'，'MM / DD / YYYY HH：MI：SS AM'）然后结束）作为shift1B 
来自计划生产时间
其中machine = '500'

如果重要，我们目前仍在运行Oracle 9。

解决方案

既然你说TRANSPOSE（PIVOT）在9i中不起作用，你可以试试这个来代替CASE语句。虽然有子查询但比在这个特定的上下文中使用CASE更容易。 （只需检查部分to_char（< date>，'day'） - 它会返回星期几 - 我在Google上获得了这一点）

 选择 mon。*，tue。*，wed。* .. sun。* 
 来自 
（
 选择机器，to_char（oeedate，' < span class =code-string> mm / dd / yyyy'）星期一，shift1_mon，shift2_mon，shift3_mon 
 来自 plannedproductiontimes 
 其中 upper（substr（to_char（oeedate，'  day '）， 1 ， 3 ））= '  MON' 
 和 machine = '  500' 
）mon，
（
 选择 to_char（oeedate，'  mm / dd / yyyy'）星期二，shift1_tue，shift2_tue ，shift3_tue 
 来自 plannedproductiontimes 
 其中 upper（substr（to_char（oeedate，< span class =code-string>'  day'）， 1 ， 3 ））= '  TUE' 
 和 machine = '  500' 
）星期二，
 ... 
 ... 
（
）sun 
 其中 to_date（星期二，'  mm / dd / yyyy'）= to_date（星期一，'  mm / dd / yyyy'）+  1  
 和 to_date（星期三，'  mm / dd / yyyy'）= to_date（星期一，< span class =code-string>'  mm / dd / yyyy'）+  2  
 ... 
 ... 
 和 to_date（星期日，'  mm / dd / yyyy'）= to_date（星期一，'  mm / dd / yyyy'）+  6  
 订单 按机器，to_date（星期一，'  mm / dd / yyyy'）
; 

您好，



我认为 PLSQL PIVOT CLAUSE 正是您正在尝试做的事情。



我建议你看看并尝试一下。您可能需要合并来自旋转表的结果，但它可以解决问题。



发布问题更新：



我认为你的查询正确，你只需要形成一个更好的查询。

你可以从其中（手动转动）或这里。



你可能需要花一些时间来弄清楚如何以你想要的方式汇总你的结果，尽管如此，这是要走的路。



谢谢，祝你好运。

I have an Oracle table with the following rows:

Machine
OEEDate
Shift1
Shift2
Shift3

The data is has looks similar to this:
500, 8/1/2017, 0, 0, 1
500, 8/2/2017, 1, 1, 1

I have a whole years worth of data in the table. I also have about 50 different machines for each date. I would like to make a query where I can display a whole weeks worth of data (7 days Monday-Sunday) for a particular machine but have the data appear in columns versus multiple rows. Here is what I am looking for. To simplify things, I am only showing 2 days worth of data. Since the same column name is being repeated, I will need to rename each column for each day.

machine, oeedate-M, Shift1-M, Shift2-M, Shift 3-M, oeedate-T, Shift1-T, Shift3-T

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

What I have tried:

I think this would be an application for using the case statement but I cannot figure out the correct syntax. I have only used the Case statement once before so I am pretty new using it. This is what I have tried but it is not working out:

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'

In case it matters, we are still running Oracle 9 at the moment.

解决方案

Since you say TRANSPOSE (PIVOT) does not work in 9i you can try this in lieu of CASE statement. Though there are subqueries it is easier than using CASE in this particular context. (Just check the part to_char(<date>, 'day') - that it returns Day of Week - I got that on 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')
; 

Hello,

I think the PLSQL PIVOT CLAUSE does precisely what you're trying to do here.

I suggest you take a look and try it. You may have to combine the results from your pivoted tables but it does the trick.

Post question update:

I think you're on the right track with your query, you just need to form a better query.
You can take some ideas from here (manual pivoting) or here.

You may have to spend some time figuring out how to aggregate your results the way you want, nonetheless, this is the way to go.

Thanks and good luck.

这篇关于每个列具有不同where子句的Oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！