试图在 oracle 中向主查询添加 2 个计数子查询时遇到了困难

[ad_1]

我尝试更新现有查询以包含 2 个子查询，每个子查询计算不同的计数。主查询自行运行良好，并且我已经能够让 2 个子查询自行运行，但是，当我尝试加入它们时，我收到语法错误“缺少关键字”。

这是我的主要查询。它从 3 个单独的表中获取检查数据，并按作业编号进行汇总，以显示每次运行检查程序的情况。根据特征类型（孔、z 轴或角）将检查数据分为不同的表。每个职位编号的结果只能在这 3 个表中的 1 个或多个表中找到。因此，这就是主查询中有一个 MIN 函数的原因，因此我最终得到每个作业编号一个条目。另一件需要注意的事情是，每个表可以具有一个或多个相同类型的探针特征，不同之处在于不同的位置。

SQL

SELECT MIN(t.timeofentry) as timeofentry, t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber 
from (
     SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      )t 
      GROUP BY t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber
      ORDER BY timeofentry

这将返回如下数据：
输入时间、机器、磁带、零件编号、主轴、作业编号
2024 年 1 月 2 日 11:13:50 下午，NC-537、4479、ABC、LH、75576
2024 年 1 月 2 日 11:25:50 下午，NC-537、4479、ABC、RH、75577

现在，我想向查询添加 2 个计数，以显示针对该作业编号检查的功能总数以及超出该作业编号容差的功能总数。像这样的东西：

进入时间、机器、磁带、零件编号、主轴、作业编号、失败、总计
2024 年 1 月 2 日 11:13:50 下午，NC-537、4479、ABC、LH、75576、0、4
2024 年 1 月 2 日 11:25:50 下午，NC-537、4479、ABC、RH、75577、2、4

这是每个的子查询。对于包含失败功能的功能，我认为我需要在某处进行 COALESCE，因为并非每个作业都有失败功能，因此子查询有时可能会返回 null

这一项包括失败的功能数量：

SQL

select jobnumber, sum(fails)as TotalFails
from(
select jobnumber, count(*) as fails from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and (xoutoftol = 'Yes' or youtoftol = 'Yes') group by jobnumber
union
select jobnumber, count(*) as fails from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and Zoutoftol = 'Yes' group by jobnumber
union
select jobnumber, count(*) as fails from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and (xoutoftol = 'Yes' or youtoftol = 'Yes') group by jobnumber)
group by jobnumber

这一项包括功能总数：

SQL

select jobnumber, sum(features)as TotalFeatures
from(
select jobnumber, count(*) as features from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
union
select jobnumber, count(*) as features from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
union
select jobnumber, count(*) as features from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber)
group by jobnumber

我尝试过的：

这是当我收到语法错误“缺少关键字”时我正在尝试的操作。我只是想先添加全部功能，然后再添加失败的功能，但我似乎无法仅让全部功能发挥作用。

SQL

SELECT MIN(t.timeofentry) as timeofentry, t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber, TF.totalfeatures
from (
     SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      )t
      left join
      (select jobnumber, sum(features)as TotalFeatures
      from(
      select jobnumber, count(*) as features from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
      union
      select jobnumber, count(*) as features from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
      union
      select jobnumber, count(*) as features from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber)
      group by jobnumber) as TF
      on t.jobnumber = TF.jobnumber
      GROUP BY t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber, tf.totalfeatures
      ORDER BY timeofentry