在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.

最满意答案

在这里，假设Id是用户的ID：

select id, datelog, min(timelog) as TimeIn, max(timelog) as TimeOut from tablename group by id, datelog

Here you are, assuming Id is the User's ID :

select id, datelog, min(timelog) as TimeIn, max(timelog) as TimeOut from tablename group by id, datelog在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.

最满意答案

在这里，假设Id是用户的ID：

select id, datelog, min(timelog) as TimeIn, max(timelog) as TimeOut from tablename group by id, datelog

Here you are, assuming Id is the User's ID :

select id, datelog, min(timelog) as TimeIn, max(timelog) as TimeOut from tablename group by id, datelog