1，json字段的使用
select person.id as userid, vUserName as 姓名,downinfo->>'$.devkey' as devmac,if(downtype=1,"手机","钥匙") as 来源,downtime as 打卡时间,"下班" as 事件, if(downstatus=0,"正常","异常") as 状态 from worklog JOIN person ON worklog.userId=person.id WHERE (tday BETWEEN '2018-05-02' AND '2018-05-02' )
2，查询判断-输出
SELECT person.id,vUserName as 姓名,devmac as 设备标识,if(dtype=1,"手机","钥匙") as 来源, FROM_UNIXTIME(time,'%Y-%m-%d %H:%i:%s') as 打卡时间,if(wtype=0,"上班","下班") as 事件,if(status=0,"正常","异常") as 状态
from workloginfo JOIN person ON workloginfo.userId=person.id WHERE ( tday BETWEEN '2018-05-03' AND '2018-05-03')and wtype=0 ORDER BY person.id and dtype=2
3，查找相近记录的时间差
SELECT
tc.userid,
tc.username AS 姓名,
tc.wtime as 离开时间,
tc.intime as 回来时间,
FLOOR(
TIMESTAMPDIFF(SECOND, tc.wtime, tc.intime) / 60
) AS 离开时长
FROM
(
SELECT
`check`,
userId AS userid,
person.vUserName AS username,
devmac,
lastintime AS wtime,
(
SELECT
lastintime
FROM
checklog tb
WHERE
tb.userId = ta.userId
AND `check` = 0
AND (tb.time -ta.time)>=5
AND (tb.time BETWEEN UNIX_TIMESTAMP('2018-05-08 0:00:01')
AND UNIX_TIMESTAMP('2018-05-08 23:59:59'))
ORDER BY
tb.time
LIMIT 1
) AS intime
FROM
checklog ta
JOIN person ON ta.userId = person.id
WHERE
(
time BETWEEN UNIX_TIMESTAMP('2018-05-08 0:00:01')
AND UNIX_TIMESTAMP('2018-05-08 23:59:59')
)
AND `check` > 1
) tc
ORDER BY 离开时间
