SQL与if语句

Question

我想用的时候或者if语句

select 
    ID, name,Departmen, date, clock_in, clock_out, 
    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 total_telat when late <=30 from kkpsurabaya 

我想使total_telat只能算1-30min

Answer 1

使用CASE . . WHEN . . END

select ID, 
    name, 
    Departmen, date, clock_in, clock_out, 
    DATEDIFF(MINUTE, on_duty, clock_in) as late , 
    DATEDIFF(MINUTE, off_duty, clock_out) as Early, 
    CASE 
    WHEN DATEDIFF(MINUTE, on_duty, clock_in) < 30 
    THEN DATEDIFF(MINUTE,on_duty, clock_in) - DATEDIFF(MINUTE ,off_duty, clock_out) 
    END as total_telat 
from kkpsurabaya 

Answer 2

Case语句选择选择最接近如果通过了所有版本的支持。这只是你理解的一个例子。我希望学习案例能帮助你达到你所需要的。

SELECT CAST(
      CASE 
        WHEN (DATEDIFF(MINUTE,on_duty, clock_in) - DATEDIFF(MINUTE ,off_duty, clock_out))> '0' or (DATEDIFF(MINUTE,on_duty, clock_in) - DATEDIFF(MINUTE ,off_duty, clock_out)) < '31' 
        THEN 1 
        ELSE 0 
      END AS bit) as Saleable, * 
FROM Product 

Answer 3

SELECT ID 
     ,NAME 
     ,Departmen 
     ,DATE 
     ,clock_in 
     ,clock_out 
     ,DATEDIFF(MINUTE, on_duty, clock_in) AS late 
     ,DATEDIFF(MINUTE, off_duty, clock_out) AS Early 
     ,total_telat = iif(DATEDIFF(MINUTE, on_duty, clock_in)<= 30, DATEDIFF(MINUTE, on_duty, clock_in) - DATEDIFF(MINUTE, off_duty, clock_out), NULL) 
    FROM kkpsurabaya