mysql - 如何仅从 MySQL 查询中获得积极结果？

Question

我希望 (`计划的预扫描时间`-`花费的预扫描时间`) 仅为正数。如果结果是否定的，我不想要它。我已经在 SELECT 子句中尝试了 (`Pre-Scan Hours Planned`-`Pre-Scan Time Spent`)>0 但它不起作用。

 SELECT 

        `Account Executive`,
        `DC Clerk`,
        `Project Number`,
        Manufacturer,
        `Open Project Date`,
        `DUE DATE`,
        `Project Status`,

       ( `Pre-Scan Hours Planned`+
        `Radiated Emissions Hours Planned`+
        `Conducted Emissions Hours Planned`+
        `Harmonic and Flicker Hours Planned`+
        `ESD Hours Planned`+
        `RF Immunity Hours Planned`+
        `EFT Hours Planned`+
        `Surge Hours Planned`+
        `Conducted RF Hours Planned`+
        `Magnetic Field Hours Planned`+
        `Voltage Dips Hours Planned`+
        `EUT Photos and Data Sheet Hours Planned`+
        `Data Sheet Hours Planned` ) AS 'Total Planned Hours',

       ( `Pre-Scan Time Spent`+
        `Radiated Emissions Time Spent`+
        `Conducted Emissions Time Spent`+
        `Harmonic and Flicker Time Spent`+
        `ESD Time Spent`+
        `RF Immunity Time Spent`+
        `EFT Time Spent`+
        `Surge Time Spent`+
        `Conducted RF Time Spent`+
        `Magnetic Field Time Spent`+
        `Voltage Dips Time Spent`+
        `EUT Photos and Data Sheet Time Spent`+
        `Data Sheet Time Spent` ) AS 'Total Spent Hours',

        ( (`Pre-Scan Hours Planned`-`Pre-Scan Time Spent`)+
        (`Radiated Emissions Hours Planned`-`Radiated Emissions Time Spent`) +
        (`Conducted Emissions Hours Planned`-`Conducted Emissions Time Spent`) +
        (`Harmonic and Flicker Hours Planned`-`Harmonic and Flicker Time Spent`) +
        (`ESD Hours Planned`-  `ESD Time Spent`) +
        (`RF Immunity Hours Planned`-`RF Immunity Time Spent`) +
        (`EFT Hours Planned`-  `EFT Time Spent`) +
        (`Surge Hours Planned`-`Surge Time Spent`) +
        (`Conducted RF Hours Planned`-`Conducted RF Time Spent`) +
        (`Magnetic Field Hours Planned`-`Magnetic Field Time Spent`)+
        (`Voltage Dips Hours Planned`-`Voltage Dips Time Spent`) +
        (`EUT Photos and Data Sheet Hours Planned`-`EUT Photos and Data Sheet Time 
        Spent`) +
        (`Data Sheet Hours Planned` -`Data Sheet Time Spent`)) AS 'Remaining Hours',

        (`Pre-Scan Status`+
        `Radiated Emissions Status`+
        `Conducted Emissions Status`+
        `Harmonic and Flicker Status`+
        `ESD Status`+
        `RF Immunity Status`+
        `EFT Status`+
        `Surge Status`+
        `Conducted RF Status`+
        `Magnetic Field Status`+
        `Voltage Dips Status`+
        `EUT Photos Status`+
        `Data Sheet Status`) AS 'Items Remaining'

    FROM 
        EMCtestplan

<小时/>

让我更好地解释一下，例如我们有以下查询:

我需要这个:

select
a,
b,
c,
(f1-f2)+(f3-f4)+(f5-f6) as Sum
from table

我想要(f1-f2)+(f3-f4)+(f5-f6)作为Sum此代码仅计算正公式。例如，如果 (f3-f4) 为负数，则不计算总和，只计算 (f1-f2)+(f5-f6)

请有人帮助我!

Answer 1

当它为负数时你想用什么？我假设您想使用零。变化:

(`Pre-Scan Hours Planned`-`Pre-Scan Time Spent`)

至:

(CASE WHEN `Pre-Scan Hours Planned`-`Pre-Scan Time Spent` > 0
      THEN (`Pre-Scan Hours Planned`-`Pre-Scan Time Spent`)
      ELSE 0 END)

如果您愿意，可以使用零以外的其他值。