从数据库中获取最小输入时间和最大输出时间 [英] 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屋!
查看全文