SQL firebird不選擇0的行

這很好用，除了在結果中，我不想要 STILL_REQUIRED 為 0 的行。

我正在使用火鳥。

Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, 
      START_OFFSET_MINS, 
      TOTAL_MINUTES_REQ - 
            (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0) 
             FROM TABLE_PROCESSTIMES 
             WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
            ) AS STILL_REQUIRED 
FROM TABLE_PROCESSES 
ORDER BY JOBID, ORDERNUM;

最簡單的是將它嵌套在另一個具有適當條件的選擇中：

select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, START_OFFSET_MINS, STILL_REQUIRED 
from (
   Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, 
          START_OFFSET_MINS, 
          TOTAL_MINUTES_REQ - 
                (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0) 
                 FROM TABLE_PROCESSTIMES 
                 WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
                ) AS STILL_REQUIRED 
   FROM TABLE_PROCESSES 
   ORDER BY JOBID, ORDERNUM
) a
where still_required <> 0

如果將內聯選擇移動到 from 子句，則可以將條件添加到 where 子句，而無需將其嵌套在另一個選擇中。

Select TP.ID, 
      TP.JobID, 
      TP.NAME, 
      TP.ORDERNUM, 
      TP.STARTAFTER, 
      TP.FINISHBEFORE, 
      TP.START_OFFSET_MINS, 
      TPT.STILL_REQUIRED 
FROM TABLE_PROCESSES TP,
    (SELECT TP.TOTAL_MINUTES_REQ - COALESCE(SUM(TPT.END_TIME-TPT.START_TIME) / 60, 0) 
             FROM TABLE_PROCESSES TP,
                  TABLE_PROCESSTIMES TPT
             WHERE TPT.PROCESSID =TP.ID 
             AND   TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
            ) TPT
WHERE TPT.PROCESSID =TP.ID 
AND   TPT. still_required != 0
ORDER BY TP.JOBID, 
        TPORDERNUM;

引用自：https://dba.stackexchange.com/questions/224907