分享几个mysql 查询

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 离开时间