sql。语法选择执行算术运算

Question

我希望在选择syntac sql中执行算术运算，如10减5 = 5，但我的选择不起作用。

select DATEDIFF(MINUTE,on_duty, clock_in) as late, DATEDIFF(MINUTE ,off_duty, clock_out) as Early, late - Early as jajalan from kkpsurabaya 

look image pls

Answer 1

你不能使用函数的结果在同一个选择，因为you cannot refer to an alias外SELECT和ORDER BY由于方式的查询进行解析。你可以调用同一个函数多次：

select DATEDIFF(MINUTE,on_duty, clock_in) as late, 
     DATEDIFF(MINUTE ,off_duty, clock_out) as Early, 
     DATEDIFF(MINUTE,on_duty, clock_in)- DATEDIFF(MINUTE ,off_duty, clock_out) as jajalan 
from kkpsurabaya 

或公共表expreession：

WITH CTE AS 
(
    SELECT DATEDIFF(MINUTE,on_duty, clock_in) as Late, 
    DATEDIFF(MINUTE ,off_duty, clock_out) as Early, 
    t.* 
    FROM kkpsurabaya t 
) 
SELECT Late, Early, Late - Early as jajalan 
FROM CTE