assume that the table structure is as follows
CREATE TABLE `signs` (
`userid` int(11) NOT NULL,
`signtime` int(11) NOT NULL,
`type` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`userid`,`signtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
you can use the following SQL to solve
select tmp.*,
max_signout-min_sign diff -sharp
from (
select
date(FROM_UNIXTIME(signtime)) day ,
userid,
min(
case type
when 0 then
signtime
when 1 then
-sharp
100000000000
end
) min_sign, -sharp
max(
case type
when 0 then
0
when 1 then
signtime
end
) max_signout -sharp
from signs
group by 1 ,2
) tmp
order by diff desc ;
but it doesn't take into account those who sign in across the day, such as clocking in at 9: 00 in the morning and getting off work at 7: 00 the next morning.
this requirement is actually a federated query using the same table as aliased, but because your signtime is of type int, it is impossible to determine "what is the same day?" in time format. Oh, this condition.
as for the rules of your signtime, you need to post the details.
this watch is not well designed. However, according to the existing table structure, you can consider the situation of check-in form check-in left join check-in form return (using left join is to consider the case of check-in or non-check-out). Like select. From check-in form A left join sign-in form B On B.userid=A.userid and B. Date = A. Date and A.type=0 and B.type=1.
recommend other solution 1: if you can slightly change the table structure, add the "check-in date" field, trade space for time, and do a composite index on the userid and "check-in date" fields, the above query will be very fast. And in the daily system, it is often necessary to judge whether the user has checked in on the same day, and the speed of query judgment with this composite index will be very fast.
recommend other solutions 2: if the table structure can be greatly changed, this requirement can be designed as follows:
check-in table
{
user id
userid
check-in date
check-in time signtime timestamp type int
check-out time
}
that is, add the "check-in date" field, and then change the "check-in type" to "check-out time". This design is simple and efficient. However, it cannot record the exception of the same user signing out twice on the same day. At this time, you can add the "user exception check-in table" or "log table" to record the user's abnormal operations, while the "check-in table" records only valid data.
