使用union查找3个表中的数据但只获得最小日期 [英] Find data from 3 tables using union but only get minimum date

问题描述

我有3张相似的桌子：



桌子PROBEVALIDATIONZAXIS

栏目：

timeofentry

jobnumber

zhit

机器



表PROBEVALIDATIONHOLE

栏目：

timeofentry

jobnumber

zhit

机器



表PROBEVALIDATIONCORNER

栏目：

timeofentry

jobnumber

zhit

机器



我想为某台机器显示一个不同的工作号码，但我也想显示一个日期。我遇到的问题是，如果我在select语句中包含timeofentry，那么我的select不再显示不同的作业号，因为每个表中的输入时间不同。如何让它显示所有3列的最短日期以及作业号？



如果我现在运行查询（见下文），我得到了关注：



11/14/2016 05:52:10 AM 28810

11/14/2016 05:52:16 AM 28810

11/14/2016 05:52:24 AM 28810

11/14/2016 06:52:10 AM 28811

11 / 14/2016 06:52:16 AM 28811

11/14/2016 06:52:24 AM 28811





我想要的是每个工作号码的最短日期结果：

11/14/2016 05:52:10 AM 28810

11/14 / 2016 06:52:10 AM 28811





我还应该补充说我最初使用union命令作为减号命令，不过这个仅当数据在所有3个表中时才有效。如果它只是在1或2个表中，那么我没有得到所有结果



我尝试过：

I have 3 tables that are similar:

Table PROBEVALIDATIONZAXIS
Columns:
timeofentry
jobnumber
zhit
machine

Table PROBEVALIDATIONHOLE
Columns:
timeofentry
jobnumber
zhit
machine

Table PROBEVALIDATIONCORNER
Columns:
timeofentry
jobnumber
zhit
machine

I want to display a distinct jobnumber for a certain machine but I also want to display a date. The problem I am having is that if I include the timeofentry in the select statement, then my select no longer displays a distinct job number because the time of entry is different in each table. How can I get it to display the min date from all 3 columns along with the job number?

If I run the query now (see below), I get the following:

11/14/2016 05:52:10 AM 28810
11/14/2016 05:52:16 AM 28810
11/14/2016 05:52:24 AM 28810
11/14/2016 06:52:10 AM 28811
11/14/2016 06:52:16 AM 28811
11/14/2016 06:52:24 AM 28811


What I want is the result with the minimum date for every job number:
11/14/2016 05:52:10 AM 28810
11/14/2016 06:52:10 AM 28811


I should also add that I originally had the union commands as minus commands, however this only works if the data is in all 3 tables. If it is just in 1 or 2 tables, then I don't get all the results

What I have tried:

Select timeofentry, JOBNUMBER
FROM PROBEVALIDATIONHOLE
WHERE HOLENUMBER = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
union
SELECT timeofentry, JOBNUMBER
FROM PROBEVALIDATIONZAXIS
WHERE ZHIT = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
union
SELECT timeofentry, JOBNUMBER
FROM PROBEVALIDATIONCORNER
WHERE TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'

推荐答案

你有没有这样试过

Did you tried like this

select t.JOBNUMBER, MIN(t.timeofentry) from (
Select timeofentry, JOBNUMBER
FROM PROBEVALIDATIONHOLE
WHERE HOLENUMBER = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
union
SELECT timeofentry, JOBNUMBER
FROM PROBEVALIDATIONZAXIS
WHERE ZHIT = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
union
SELECT timeofentry, JOBNUMBER
FROM PROBEVALIDATIONCORNER
WHERE TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
)t group by t.JOBNUMBER

尝试使用如下内联视图

Have a try with an inline view like the following

select a.jobnumber, min(a.timeofentry)
from (Select timeofentry, JOBNUMBER
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
      union
      SELECT timeofentry, JOBNUMBER
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
      union
      SELECT timeofentry, JOBNUMBER
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500') a
group by a.jobnumber

这篇关于使用union查找3个表中的数据但只获得最小日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！