如何根据ID将两行数据合并为一行

Question

大家好，我正在尝试根据我的要求获得数据。我的表中的数据如下

mpCode in/outMode         date                   time
 5001        0               12/09/2011    09:05:34
5002        0               12/09/2011    09:33:13
5001        1               12/09/2011    18:05:09
5002        1               12/09/2011    17:44:34

我希望我的输出为

  empCode  date             intime        outtime
  5001       12/09/2011    09:05:34    18:05:09
  5002       12/09/2011    09:33:13    17:44:34

Answer 1

如果没有额外的信息，GROUP BY可能是最简单的:

SELECT   empcode, 
         MAX([date]) as [date], 
         MAX(intime) as [intime], 
         MAX(outtime) as [outtime]
FROM MyTable
GROUP BY empcode

Answer 2

如上所述，有一些陷阱(例如没有进/出，一天进一天出)，但这是您所描述的。

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp (EmpCode INT, InOutMode INT, [DATE] DATETIME, [TIME] DATETIME)
GO
INSERT #tmp (EmpCode,InOutMode,[Date],[TIME])
VALUES (5001,0,'12/09/2011','1/1/1900 09:05:34')
,(5002,0, '12/09/2011','1/1/1900 09:33:13')
,(5001, 1,'12/09/2011','1/1/1900 18:05:09')
,(5002,1 ,'12/09/2011','1/1/1900 17:44:34');
WITH InTime (EmpCode,Date,Time)
AS
(
SELECT
    EmpCode
    ,DATE
    ,TIME
FROM #tmp
WHERE InOutMode = 0
)
SELECT
    t.EmpCode
    ,t.[DATE]
    ,i.[Time]
    ,t.[Time]   
FROM #tmp AS t
JOIN InTime AS i
ON t.EmpCode = i.EmpCode
   AND t.[Date] = i.[Date]
WHERE t.InOutMode = 1