在一个表中选择两行相同的时间

Question

I have this table:

USERID  CHECKTIME                CHECKTYPE
1       2018-10-21 09:01:08.000  I
1       2018-10-21 10:00:51.000  I
1       2018-10-22 13:19:23.000  1
1       2018-10-22 15:56:11.000  0
1       2018-10-22 16:51:27.000  O

I need select two rows like this:

userid   in                         out
1        2018-10-22 13:19:23.000    2018-10-22 15:56:11.000   

but same the date and same userid date and userid come from post page php database is sqlserver connection with php

SELECT TOP 1 checktime, userid, checktype
FROM CHECKINOUT 
WHERE userid = '$idd' 
    AND CAST(checktime As DATE)='$x'
    AND checktype='1' 
UNION ALL
SELECT TOP 1 checktime, userid, checktype
FROM CHECKINOUT 
WHERE userid='$idd'
    AND CAST(checktime As DATE)='$x'
    AND checktype='0'

Answer 1

Table structure

USERID : int(11)

CHECKTIME : datetime

CHECKTYPE : char(2)

Query

SELECT USERID, MAX(`IN`) AS 'IN', MAX(`OUT`) AS 'OUT'
 FROM
(
    SELECT USERID
         , CASE WHEN CHECKTYPE = '1' THEN CHECKTIME ELSE '0000-00-00 00:00:00.000' END AS 'IN'
         , CASE WHEN CHECKTYPE = '0' THEN CHECKTIME ELSE '0000-00-00 00:00:00.000' END AS 'OUT'
    FROM CHECKINOUT
     WHERE CHECKTYPE IN ('0', '1')
) U
 GROUP BY U.USERID

I checked in my local and this query worked well.

Explanation

Sub query U

It collect CHECKTIME for IN, and OUT by CHECKTYPE condition. When no IN data, the default value will be "0000:00:00 ....", and OUT is also.

In outer query

It will get max value (reject "0000:00:00 ..."), and group records, so we can get correct data.

I suggest using GROUP BY instead of DISTINCT, because it makes Query run faster than DISTINCT.