试试这个：
样本数据
CREATE TABLE tblAccess( EmployeeID VARCHAr(20), EmployeeName VARCHAr(20), Location VARCHAr(20), AccessTime DATETIME, ReaderType VARCHAr(3))INSERT INTO tblAccess VALUES('_1346', 'A', 'L1', '7/11/2014 10:00', 'IN'),('_1347', 'B', 'L2', '7/10/2014 10:58', 'IN'),('_1346', 'A', 'L3', '7/11/2014 23:39', 'OUT'),('_1347', 'B', 'L4', '7/10/2014 23:58', 'OUT'),('_1364', 'C', 'L5', '7/11/2014 10:00', 'IN'),('_1367', 'D', 'L6', '7/10/2014 10:58', 'IN'),('_1367', 'D', 'L7', '7/10/2014 22:42', 'OUT'),('_1364', 'C', 'L8', '7/11/2014 23:58', 'OUT'),('_1422', 'E', 'L9', '7/11/2014 23:58', 'IN'),('_1422', 'E', 'L10', '7/11/2014 23:10', 'IN'),('_1111', 'F', 'L20', '7/10/2014 23:10', 'OUT');解决方案
;WITH CTE AS( SELECt EmployeeID, EmployeeName, AccessDate = CAST(AccessTime AS DATE), AccessTime = CAST(AccessTime AS TIME), Location, ReaderType, In_RN = ROW_NUMBER() OVER(PARTITION BY EmployeeId, CAST(AccessTime AS DATE), ReaderType ORDER BY CAST(AccessTime AS TIME) ASC), Out_RN = ROW_NUMBER() OVER(PARTITION BY EmployeeId, CAST(AccessTime AS DATE), ReaderType ORDER BY CAST(AccessTime AS TIME) DESC) FROM tblAccess)SELECt EmployeeID, EmployeeName, [Date] = ConVERT(VARCHAr(10), AccessDate, 101), InLoc = ISNULL(MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN Location END), ''), InTime= ISNULL(SUBSTRINg(ConVERT(VARCHAr(20), MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN AccessTime END)), 1, 5), ''), OutLoc = ISNULL(MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN Location END), ''), OutTime = ISNULL(SUBSTRINg(ConVERT(VARCHAr(20), MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN AccessTime END)), 1, 5), ''), Duration = ISNULL(RIGHt('00' + ConVERT(VARCHAr(2), DATEDIFF(MINUTE, MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN AccessTime END), MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN AccessTime END) )/60), 2) + ':' + RIGHt('00' +ConVERT(VARCHAr(2), DATEDIFF(MINUTE, MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN AccessTime END), MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN AccessTime END) )%60), 2) ,'')FROM CTEGROUP BY EmployeeID, EmployeeName, AccessDateORDER BY EmployeeName, AccessDate结果
EmployeeIDEmployeeName Date InLoc InTime OutLoc OutTime Duration-------------------- ------------ ---------- -------- ------ ------- ------- --------_1346 A 07/11/2014 L1 10:00 L3 23:39 13:39_1347 B 07/10/2014 L2 10:58 L4 23:58 13:00_1364 C 07/11/2014 L5 10:00 L8 23:58 13:58_1367 D 07/10/2014 L6 10:58 L7 22:42 11:44_1422 E 07/11/2014 L10 23:10 _1111 F 07/10/2014 L20 23:10
