All geek questions in one place

Two row time sorting

I have an SQL table:

+----+-----+-------+-------+
| id | reg |  in   | out   |
+----+-----+-------+-------+
|  1 | a   | 11:10 |       |
|  2 | a   |       | 11:30 |
|  3 | b   | 06:10 |       |
|  4 | c   |       | 07:10 |
+----+-----+-------+-------+

I have sorting by array, so it will match if the reg is the same, and before it was the same as for example:

array( 
[0]=> array('reg'=>'a','in'=>'11:10','out'=>'11:30'),
[1]=> array('reg'=>'b','in'=>'06:10','out'=>''),
[2]=> array('reg'=>'c','in'=>'','out'=>'07:10')
)

Then I need to resort to this array in order of time, but where there is an entry and exit time, use the exit time. Therefore, if it was sorted, it would be:

 array(
    [0]=> array('reg'=>'b','in'=>'06:10','out'=>''),
    [1]=> array('reg'=>'c','in'=>'','out'=>'07:10'), 
    [2]=> array('reg'=>'a','in'=>'11:10','out'=>'11:30')
    )

I hope this makes sense, I have about 200 entries a day, and I spent my entire weekend trying to get him to order, but it seems to always be weird.

Times will be the unix timestamp I just put in H: here, when SQL will sort WHERE> midnight and <00:01, that morning only shows the current day.

Perhaps something really simple and obvious is looking at me, but I do not see it.

+4
2

SQL, , , out , in :

select reg, max(`in`) as maxin, max(`out`) as maxout from table
group by reg
order by greatest(maxin,ifnull(maxout,0))
+1

IF():

SELECT `reg`,`in`,`out`, IF(`out`='00:00:00', `in`,`out`) AS sortfield 
FROM `test` 
ORDER BY sortfield

, in out TIME. , out 00:00:00, , . , in, out, .

, -out 00:00:00 , NULL, .

DATETIME .

0

Source: https://habr.com/ru/post/1619900/

More articles:


All Articles