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

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'

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.

Here you go

CREATE TABLE T
    ([UserId] int, [CheckDate] datetime, [CheckType] BIT)
;

INSERT INTO T
    ([UserId], [CheckDate], [CheckType])
VALUES
    (1, '2018-10-21 09:01:08', 1),
    (1, '2018-10-21 10:00:51', 1),
    (1, '2018-10-22 13:19:23', 1),
    (1, '2018-10-22 15:56:11', 0),
    (1, '2018-10-22 16:51:27', 0)
;

SELECT DISTINCT UserID,
       (SELECT MAX(CheckDate) FROM T WHERE CheckType = 1 ANDUserID = TT.UserID) [In],
       (SELECT MIN(CheckDate) FROM T WHERE CheckType = 0 AND UserID = TT.UserID) [Out]
FROM T TT
WHERE UserID = 1;

You have wrong data in your sample (I guess), cause in your query you check for '1' and '0' in your query, which mean there is no 'I', so you can use BIT datatype instead of VARCHAR.

If you really have 'I' in the check type so it would be

SELECT DISTINCT UserID,
       (SELECT MAX(CheckDate) FROM T WHERE CheckType = '1' AND UserID = TT.UserID) [In],
       (SELECT MIN(CheckDate) FROM T WHERE CheckType = '0' AND UserID = TT.UserID) [Out]
FROM T TT
WHERE UserID = 1;

Results