mysql查詢計(jì)算排序的問(wèn)題

假設(shè)表結(jié)構(gòu)如下

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  #簽退時(shí)間差
    from (
    select 
    date(FROM_UNIXTIME(signtime)) day , 
    userid,
    min(
        case type
        when 0 then
         signtime
        when 1 then
            #這里表示的是一個(gè)無(wú)窮大的意思，反正就是讓簽退時(shí)間不參與比較，
         100000000000
        end
    ) min_sign, #一天中的最早的簽到時(shí)間
    max(
        case type
        when 0 then
         0
        when 1 then
         signtime
        end
    ) max_signout #一天中的最晚簽到時(shí)間
    from signs 
    group  by 1 ,2
) tmp 
order by diff desc ;

不過(guò)沒(méi)有考慮到跨天簽到的，比如早上9點(diǎn)打卡，第二天上午7點(diǎn)下班的情況。。。。

這個(gè)需求其實(shí)就是用同一個(gè)表以 aliased 方式進(jìn)行聯(lián)合查詢而已，但是，由于你的 signtime 是 int 類型，所以，無(wú)法按照時(shí)間的格式來(lái)確定“何為同一天？”，這個(gè)條件。

至于你的 signtime 有啥規(guī)律，這得需要你把詳細(xì)的內(nèi)容貼出來(lái)。

這表設(shè)計(jì)的不好。不過(guò)按現(xiàn)有這種表結(jié)構(gòu)，你可以考慮簽到表簽到的情況left join簽到表簽退的情況（用left join是考慮有簽到?jīng)]有簽退的情況）。比如select .. from 簽到表 A left join 簽到表 B On B.userid=A.userid and B.日期=A.日期 and A.type=0 and B.type=1 ...

推薦其它方案1：如果可以稍微改一下表結(jié)構(gòu)，增加“簽到日期”欄位，用空間換時(shí)間，而且對(duì)userid和“簽到日期”兩個(gè)欄位做一個(gè)復(fù)合索引，以上的查詢就會(huì)很快。而且日常系統(tǒng)中，經(jīng)常也要判斷用戶同一天是否已經(jīng)簽到過(guò),有這個(gè)復(fù)合索引查詢判斷的速度就會(huì)很快。

推薦其它方案2：如果表結(jié)構(gòu)可以大改，這種需求可以這樣設(shè)計(jì)：
簽到表
{
用戶id
userid
簽到日期
簽到時(shí)間 signtime 時(shí)間戳類型 int
簽退時(shí)間
}
也就是增加“簽到日期”欄位，然后把“簽到類型”改成“簽退時(shí)間”，這樣的設(shè)計(jì)簡(jiǎn)單又高效。但是它不能記錄同一個(gè)用戶同一天兩次簽退的異常情況，這時(shí)候可以增加“用戶異常簽到表”或者“日志表”來(lái)記錄用戶的異常操作，而“簽到表”只記錄有效的數(shù)據(jù)。