mysql 查询邻近的两条记录并合并成一条

来源：互联网发布：电源监控软件编辑：程序博客网时间：2024/05/23 19:05

现有一个签到表，需要查出每个人的每次签到和签出时间

t_sign表结构和数据如下idusertypetime1Ain2016-10-10 09:00:002Bin2016-10-10 09:05:003Bout2016-10-10 10:00:005Aout2016-10-10 11:27:006Ain2016-10-10 13:57:008Bin2016-10-10 16:08:009Aout2016-10-10 17:45:0010Ain2016-10-10 18:01:0011Bout2016-10-10 18:02:00

希望查询结果userin_timeout_timeA2016-10-10 09:00:002016-10-10 11:27:00A2016-10-10 13:57:002016-10-10 17:45:00A2016-10-10 18:01:00NULLB2016-10-10 09:05:002016-10-10 10:00:00B2016-10-10 16:08:002016-10-10 18:02:00

SQL：

select user, time as in_time,(select time from t_sign where user=s.user and type='out' and time > s.time order by time limit 1) out_timefrom t_sign s where type = 'in' order by user, time

写这次文章主要是想记这个SQL，多一个思路：

SELECT USER, MIN(TIME) in_time, IF(MIN(TYPE)!=MAX(TYPE),MAX(TIME),NULL) out_time FROM(SELECT @group_row:=CASE WHEN @user=s.user THEN  @group_row+1 ELSE 1 END AS group_row, --签到顺序CEIL(@group_row / 2) sign_time,--第几轮签到@user:=s.user AS USER,s.time,s.typeFROM t_sign s, ( SELECT @group_row:=1, @user:='') AS rORDER BY s.user, s.time) t GROUP BY USER, sign_time --按轮数分组，取出每轮分组的最大和最小时间

0 0