mysql查询计算排序的问题

Question

签到表：字段有
用户id userid
签到时间 signtime 时间戳类型 int
签到类型 type 分为 0和1 分别代表 签到和签退， 一个用户一天可以签两次， 一次 签到 一次签退。 签到和签退都是必须在一天，不考虑不在一天。

现在想要把签到 和签退 之间的时间（两个时间相减）查出来 ，并且按照这个时间排序， 最后分组给每个用户。
这还需要考虑到， 这个用户有签到， 没签退的情况 。
主要是判断如果这个用户都有签到和签退就把两个时间的差值求出来 这个弄出来就好了
这如何写sql计算呢

Answer 1

假设表结构如下

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;

可以用如下SQL解决

select tmp.*,
    max_signout-min_sign diff  #签退时间差
    from (
    select 
    date(FROM_UNIXTIME(signtime)) day , 
    userid,
    min(
        case type
        when 0 then
         signtime
        when 1 then
            #这里表示的是一个无穷大的意思，反正就是让签退时间不参与比较，
         100000000000
        end
    ) min_sign, #一天中的最早的签到时间
    max(
        case type
        when 0 then
         0
        when 1 then
         signtime
        end
    ) max_signout #一天中的最晚签到时间
    from signs 
    group  by 1 ,2
) tmp 
order by diff desc ;

不过没有考虑到跨天签到的，比如早上9点打卡，第二天上午7点下班的情况。。。。

Answer 2

这个需求其实就是用同一个表以 aliased 方式进行联合查询而已，但是，由于你的 signtime 是 int 类型，所以，无法按照时间的格式来确定“何为同一天？”，这个条件。

至于你的 signtime 有啥规律，这得需要你把详细的内容贴出来。

Answer 3

这表设计的不好。不过按现有这种表结构，你可以考虑签到表签到的情况left join签到表签退的情况（用left join是考虑有签到没有签退的情况）。比如select .. from 签到表 A left join 签到表 B On B.userid=A.userid and B.日期=A.日期 and A.type=0 and B.type=1 ...

推荐其它方案1：如果可以稍微改一下表结构，增加“签到日期”栏位，用空间换时间，而且对userid和“签到日期”两个栏位做一个复合索引，以上的查询就会很快。而且日常系统中，经常也要判断用户同一天是否已经签到过,有这个复合索引查询判断的速度就会很快。

推荐其它方案2：如果表结构可以大改，这种需求可以这样设计：
签到表
{
用户id
userid
签到日期
签到时间 signtime 时间戳类型 int
签退时间
}
也就是增加“签到日期”栏位，然后把“签到类型”改成“签退时间”，这样的设计简单又高效。但是它不能记录同一个用户同一天两次签退的异常情况，这时候可以增加“用户异常签到表”或者“日志表”来记录用户的异常操作，而“签到表”只记录有效的数据。