用于对门接入表进行排序的Sql查询

Question

我有这样的门禁接入表

------------+--------------+----------+-----------------+------------+
| EmployeeId | EmployeeName | Location |   AccessTime    | ReaderType |
+------------+--------------+----------+-----------------+------------+
| _1341      | A            | L1       | 7/11/2014 10:00 | IN         |
| _1342      | B            | L2       | 7/11/2014 10:58 | IN         |
| _1343      | A            | L3       | 7/11/2014 13:39 | OUT        |
| _1344      | B            | L4       | 7/11/2014 13:58 | OUT        |
| _1365      | A            | L5       | 7/11/2014 14:00 | IN         |
| _1366      | B            | L6       | 7/11/2014 14:58 | IN         |
| _1367      | A            | L7       | 7/11/2014 22:42 | OUT        |
| _1368      | B            | L8       | 7/11/2014 23:58 | OUT        |
| _1369      | A            | L5       | 7/12/2014 14:00 | IN         |
| _1370      | B            | L6       | 7/12/2014 14:58 | IN         |
| _1371      | A            | L7       | 7/12/2014 15:42 | OUT        |
| _1372      | B            | L8       | 7/12/2014 16:58 | OUT        |
| _1373      | A            | L5       | 7/12/2014 18:00 | IN         |
| _1374      | B            | L6       | 7/12/2014 18:58 | IN         |
| _1375      | A            | L7       | 7/12/2014 22:42 | OUT        |
| _1376      | B            | L8       | 7/12/2014 23:58 | OUT        |
+------------+--------------+----------+-----------------+------------+

我想把它放在下面

------------+--------------+----------+-----------------+------------+
| EmployeeId | EmployeeName | Location |   AccessTime    | ReaderType |
+------------+--------------+----------+-----------------+------------+
| _1341      | A            | L1       | 7/11/2014 10:00 | IN         |
| _1341      | A            | L3       | 7/11/2014 13:39 | OUT        |
| _1341      | A            | L5       | 7/11/2014 14:00 | IN         |
| _1341      | A            | L7       | 7/11/2014 22:42 | OUT        |
| _1342      | B            | L2       | 7/11/2014 10:58 | IN         |
| _1342      | B            | L4       | 7/11/2014 13:58 | OUT        |
| _1342      | B            | L6       | 7/11/2014 14:58 | IN         |
| _1342      | B            | L8       | 7/11/2014 23:58 | OUT        |
| _1341      | A            | L5       | 7/12/2014 14:00 | IN         |
| _1341      | A            | L7       | 7/12/2014 15:42 | OUT        |
| _1341      | A            | L5       | 7/12/2014 18:00 | IN         |
| _1341      | A            | L7       | 7/12/2014 22:42 | OUT        |
| _1342      | B            | L6       | 7/12/2014 14:58 | IN         |
| _1342      | B            | L8       | 7/12/2014 16:58 | OUT        |
| _1342      | B            | L6       | 7/12/2014 18:58 | IN         |
| _1342      | B            | L8       | 7/12/2014 23:58 | OUT        |      
+------------+--------------+----------+-----------------+------------+

我想找出上面提到的细节，即找出每个员工在一天中完成的IN和out。

表参数

EmployeeID varchar(20)
EMployeeName varchar(20)
Location varchar(20)
AcessTime DateTime
ReaderType varchar(20)

请尽快帮助我们。

Answer 1

也许只需按名称/id和accessTimeStamp排序？

Answer 2

我猜诀窍是先按日期排序(没有时间)，然后是员工，最后是时间。

select EmployeeID, EmployeeName, Location, AccessTime, ReaderType
from DoorAccessTable
order by cast(AccessTime AS DATE), EmployeeID, AccessTime