我的出勤表中有3个条目,但是我需要打印出勤表中ID的最后一个条目。我在单独的查询中计算了总时长,并在单独的查询中打印了列表,任何人都可以帮助我解决这个问题。

查询以打印总持续时间:

select SEC_TO_TIME(sum(case when a.endtime  IS NULL
                         then time_to_sec(TIMEDIFF(NOW(),a.starttime))
                         else time_to_sec(a.duration)
                       end)
                   ) as duration
from attendance a
left join staff s on a.staffid=s.id
left join company c on a.companyid=c.companyid
where DATE_FORMAT(a.createdon, '%Y/%m/%d') = DATE_FORMAT('2017-04-04', '%Y/%m/%d') and
(a.employeecode ='A101' OR 'A101'='') and
(a.companyid=0 OR 0=0)
group by a.employeecode,a.companyid order by a.id desc


查询以打印详细信息:

select a.id,
       a.starttime,
       a.endtime,
       a.startlocation,
       a.endlocation,
       a.duration,
       a.companyid,
       a.employeecode,
       a.staffid,
       a.createdon,
       a.createdby,
       a.lastmodifiedon,
       a.reason,
       a.comments,
       s.name as staffName,
       c.company_name as companyName
from attendance a
left joinstaff s on a.staffid=s.id
left join company c on a.companyid=c.companyid
where DATE_FORMAT(a.created_on, '%Y/%m/%d') = DATE_FORMAT('2017-04-04', '%Y/%m/%d') and
(a.employeecode ='A101' OR 'A101'='') and
(a.company_id=0 OR 0=0)
group by a.employeecode,a.companyid order by a.id desc

最佳答案

我通过将结束时间设置为null来获得输出。

`select SEC_TO_TIME(sum(case when a.endtime IS NULL then  time_to_sec(TIMEDIFF(NOW(),a.starttime)) else time_to_sec(a.duration) end))
from attendance a left join staff s on a.staffid=s.id
left join company c on a.companyid=c.companyid
where DATE_FORMAT(a.createdon, '%Y/%m/%d') = DATE_FORMAT('', '%Y/%m/%d') and
(a.employeecode ='' OR ''='') and
(a.companyid=0 OR 0=0)
group by a.employeecode,a.companyid order by a.id desc`

关于mysql - 如何在SQL中获取表的最后一条记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43206404/

10-10 13:45