在Sql Server中填充数据(Populate Data in Sql Server)

我已经做了很多搜索，并且越来越混淆我的要求。花了大约2天的时间才找到解决方案。让我解释一下我的要求

基本上我有一张桌子

---------------------------- | Id | DateLog | TimeLog | |----|------------|----------| | 01 | 2014-06-20 | 15:15:05 | | 01 | 2014-06-20 | 17:43:21 | | 02 | 2014-06-20 | 19:12:10 | | 02 | 2014-06-20 | 13:11:00 |

我想填充上面的表格，就像这样

--------------------------------------- | Id | DateLog | TimeIn | TimeOut | |----|------------|----------|----------| | 01 | 2014-06-20 | 15:15:05 | 17:43:21 | | 02 | 2014-06-20 | 13:11:00 | 19:12:10 |

TimeIn列将基于userId的earlyIn，TimeOut将基于userId的Last Out并按其Id分组。

我尝试做这样的事情，但输出不像我想要的。

select id,DateLog, ( select top 1 Timelog From dbo.[myAttendance] order by TimeLog Asc )AS TimeIn, ( select top(1) TimeLog From dbo.[myAttendance] order by TimeLog Desc )AS TimeOut from dbo.[myAttendance] group by Id

希望你们能在这个问题上帮助我。

I already did a lot of searching and become more and more confuse on the my requirement. Took me about 2 days to find the solution. let me explain what is my requirement

basically i have one table

---------------------------- | Id | DateLog | TimeLog | |----|------------|----------| | 01 | 2014-06-20 | 15:15:05 | | 01 | 2014-06-20 | 17:43:21 | | 02 | 2014-06-20 | 19:12:10 | | 02 | 2014-06-20 | 13:11:00 |

and i want to populate that above table to be similar like this

--------------------------------------- | Id | DateLog | TimeIn | TimeOut | |----|------------|----------|----------| | 01 | 2014-06-20 | 15:15:05 | 17:43:21 | | 02 | 2014-06-20 | 13:11:00 | 19:12:10 |

TimeIn column will be based on the earlyIn of the userId and TimeOut will be based on Last Out of the userId and group them by their Id.

i tried do something like this but the output its not like what i want.

select id,DateLog, ( select top 1 Timelog From dbo.[myAttendance] order by TimeLog Asc )AS TimeIn, ( select top(1) TimeLog From dbo.[myAttendance] order by TimeLog Desc )AS TimeOut from dbo.[myAttendance] group by Id

hopefully you guys can help me on this issue.