从数据库中获取最小输入时间和最大输出时间 [英] Fetch Min In Time and Max out Time From Database

问题描述

我有一个MySQL表.我需要通过MySQL查询获取最小输入时间"和最大输出时间".我只提取IN_AM和OUT_PM.如何比较IN_AM和IN_PM以及比较两次时间和访存最短时间.和比较两次并获取最高 我的桌子是-

I have a MySQL Table. I need to Fetch "Min In Time" and "Max Out Time" by MySQL Query. I am fetching only IN_AM and OUT_PM. How to compare IN_AM and IN_PM and comparing Two time and Fetching Min time. and Comparing Two time and fetching Max. My Table is-

我已经尝试过了.但无法使用，因为列为空.

I have tried this. but not working because empty column.

SELECT *, 
       LEAST(IN_AM, IN_PM) AS intime,
       GREATEST(OUT_AM, OUT_PM) AS outtime
FROM 
       bio_attandence 
where 
       date(STR_TO_DATE(attandence_date,'%d/%m/%Y')) >= date('$startdate') 
AND 
       date(STR_TO_DATE(attandence_date,'%d/%m/%Y')) < date('$enddATE') 
AND 
       bio_id=".$row['form_no']." 
GROUP BY 
       bio_id, 
       attandence_date  
order by 
       attandence_date

推荐答案

您可以这样做:

 Select IF(IN_AM IS NULL OR IN_PM IS NULL, COALESCE(IN_AM, IN_PM), LEAST(IN_AM,IN_PM)) AS intime,
 IF(OUT_AM IS NULL OR  OUT_AM IS NULL, COALESCE(OUT_AM,  OUT_AM), GREATEST(OUT_AM, OUT_AM)) AS outtime FROM bio_attandence where date(STR_TO_DATE(attandence_date,'%d/%m/%Y')) 
 >= date('$startdate') AND date(STR_TO_DATE(attandence_date,'%d/%m/%Y')) 
 < date('$enddATE') AND bio_id=".$row['form_no']." GROUP BY bio_id, 
 attandence_date  
 HAVING intime IS NOT NULL AND outtime IS NOT NULL
 order by attandence_date

这篇关于从数据库中获取最小输入时间和最大输出时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！