在SQL Server中如何

Question

我该如何在SQL Server中执行此操作？

我有这样的桌子：

table1

userid    datetime             date         inoutmode 
-----------------------------------------------------    
123      2018-02-02 14:11:14   2018-02-02      0  
123      2018-02-02 18:52:44   2018-02-02      1 
123      2018-02-05 09:10:07   2018-02-05      0

---

0 = in 
1 = out 

我如何按这样的日期将其放在一行中：

userid    date          intime    inmod   outtime   outmod  
--------------------------------------------------------------------------
123       2018-02-02    14:11:14    0     18:52:44    1  
123       2018-02-05    09:10:07    0     (null) or 00:00:00  (null) or (9)

Answer 1

您可以使用group by

DECLARE @table1 TABLE(userid INT, [datetime] datetime, [date] date, inoutmode INT)
INSERT INTO @table1 VALUES
(123, '2018-02-02 14:11:14', '2018-02-02', 0 ), 
(123, '2018-02-02 18:52:44', '2018-02-02', 1 ),
(123, '2018-02-05 09:10:07', '2018-02-05', 0 )
select 
    userid,
    [date],
    isnull( cast( min( case when inoutmode = 0 then [datetime] end ) as time ), '00:00:00') intime,   
    isnull( min( case when inoutmode = 0 then inoutmode end ) , 0 ) inmod, 
    isnull( cast( max( case when inoutmode = 1 then [datetime] end ) as time ), '00:00:00') outtime,
    isnull( max( case when inoutmode = 1 then inoutmode end ) , 9 ) outmod 
from @table1
group by
    userid, [date]

结果：

userid      date       intime   inmod       outtime  outmod
----------- ---------- -------- ----------- -------- -----------
123         2018-02-02 14:11:14 0           18:52:44 1
123         2018-02-05 09:10:07 0           00:00:00 9

Answer 2

基于您的输入，您可以通过以下内容实现：

;WITH Table1
    AS (
        SELECT 123 userid
            ,CAST('2018-02-02 14:11:14' AS DATETIME) [datetime]
            ,CAST('2018-02-02' AS DATE) [date]
            ,0 inoutmode
        UNION ALL
        SELECT 123 userid
            ,CAST('2018-02-02 18:52:44' AS DATETIME) [datetime]
            ,CAST('2018-02-02' AS DATE) [date]
            ,1 inoutmode
        UNION ALL
        SELECT 123 userid
            ,CAST('2018-02-05 09:10:07' AS DATETIME) [datetime]
            ,CAST('2018-02-05' AS DATE) [date]
            ,0 inoutmode
        )
SELECT [in].userid
    ,[in].[date]
    ,intime = CONVERT(TIME, CONVERT(VARCHAR(10), [in].[datetime], 108))
    ,inmod = [in].inoutmode
    ,outtime = ISNULL(CONVERT(TIME, CONVERT(VARCHAR(10), [out].[datetime], 108)), '00:00:00')
    ,outmod = ISNULL([out].inoutmode, 9)
FROM Table1 [in]
LEFT JOIN Table1 [out] ON [in].userid = [out].userid
    AND [in].DATETIME < [out].DATETIME
    AND [out].inoutmode = 1
WHERE [in].inoutmode = 0