自认为高难度SQL ,敢挑战的就进来吧.(200分)

有一个表Table1,记录的是每个员工出差时间,该表可能有几万条记录,例如.<br>Autoid &nbsp; Employeeid &nbsp; CurDate &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ST1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ET2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StartTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EndTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ST2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ET2<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 08:00:00 &nbsp; &nbsp;2008-05-03 14:00:00 &nbsp;<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 16:00:00 &nbsp; &nbsp;2008-05-03 17:00:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 21 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 10:00:00 &nbsp; &nbsp;2008-05-03 14:00:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 22 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 08:00:00 &nbsp; &nbsp;2008-05-03 11:00:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 22 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 13:00:00 &nbsp; &nbsp;2008-05-03 15:00:00<br><br>我想根据上面这个表,得出下面这个表<br><br>Autoid &nbsp; Employeeid &nbsp; CurDate &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ST1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ET2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StartTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EndTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ST2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ET2<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp;2008-05-03 08:00 &nbsp;2008-05-03 08:00 2008-05-03 08:00:00 &nbsp; &nbsp;2008-05-03 14:00:00 2008-05-03 14:00 &nbsp;2008-05-03 16:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp;2008-05-03 14:00 &nbsp;2008-05-03 16:00 2008-05-03 16:00:00 &nbsp; &nbsp;2008-05-03 17:00:00 2008-05-03 17:00 &nbsp;2008-05-03 17:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 21 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp;2008-05-03 08:00 &nbsp;2008-05-03 10:00 2008-05-03 10:00:00 &nbsp; &nbsp;2008-05-03 14:00:00 2008-05-03 14:00 &nbsp;2008-05-03 17:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 22 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp;2008-05-03 08:00 &nbsp;2008-05-03 08:00 2008-05-03 08:00:00 &nbsp; &nbsp;2008-05-03 11:00:00 2008-05-03 11:00 &nbsp;2008-05-03 13:00<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 22 &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-03 &nbsp;2008-05-03 11:00 &nbsp;2008-05-03 13:00 2008-05-03 13:00:00 &nbsp; &nbsp;2008-05-03 15:00:00 2008-05-03 15:00 &nbsp;2008-05-03 17:00<br><br>得出结果的依据为:<br>每天的工作的时间从08:00至17:00.<br><br>&nbsp; &nbsp; 假如某员工一天只有一条出差记录,时间为11:00至15:00,那我必须记录得该员工出差前的出勤开始时间与结束时间,及出差回公司<br>后出勤的开始时间与结果时间.根据上班时间08:00~17: 00可以知道,出差前的开始出勤时间为08:00,结束时间为11:00,出差后的开始出勤时间为15:00,结束时间为17:00.<br>&nbsp; &nbsp;<br>&nbsp; &nbsp; 假如某员工一天有一条以上的出差记录,比如时间为09:00至11:00,15:00至16:00,那这个员工第一条出差记录的出差前开始出勤时间为08:00,结束出勤时间为09:00,<br>出差后的开始出勤时间为11:00,结束出勤时间为15:00.第二条出差记录的出差前开始出勤时间为11:00,结束出勤时间为15:00,出差后开始出勤时间为16:00,结束出勤时间<br>为17:00,不知各位有没有理解我的意思呢?

一个多小时过去了,连一个回复的都没有,有点失望.

你的QQ名称是不是 "245389505"？

不是

好久没写SQL了，都快忘了。怎么感觉你给的数据看起来这么别扭

呵呵!我觉得设计和需求都怪怪地

需求有点看不明白，请楼主把需求的描述说清楚，这样大家好帮你

我只测试某员工一天只有一条或两条出差记录的情况<br>select Autoid,Employeeid,curDate,curDate+' 08:00' as Bt1, StartTime as Et1,StartTime,EndTime,EndTime as Bt1,curDate+' 17:00' as Et2<br>from table1 where &nbsp;Autoid+Employeeid+curDate in <br>(select Autoid+Employeeid+curDate from <br>(select count(*) as c,Autoid,Employeeid,curDate<br>from table1<br>group by Autoid,Employeeid,curDate<br>having count(*)=1) a) <br>--这是一天只有一条出差记录的情况<br>union<br>select Autoid,Employeeid,curDate,--curDate+' 08:00' as Bt1, StartTime as Et1,StartTime,EndTime,EndTime as Bt1,curDate+' 17:00' as Et2<br>isnull((select top 1 EndTime from table1 where Autoid=t.Autoid and Employeeid=t.Employeeid and curDate=t.curDate<br>and EndTime&lt;t.StartTime order by startTime),curDate+' 08:00') as ST1,<br>StartTime as ET1,<br>StartTime,EndTime,<br>EndTime as BT2,<br>isnull((select top 1 StartTime from table1 where Autoid=t.Autoid and Employeeid=t.Employeeid and curDate=t.curDate<br>and StartTime&gt;t.EndTime order by startTime),curDate+' 17:00') as ET2<br>from table1 t where &nbsp;Autoid+Employeeid+curDate in <br>(select Autoid+Employeeid+curDate from <br>(select count(*) as c,Autoid,Employeeid,curDate<br>from table1<br>group by Autoid,Employeeid,curDate<br>having count(*)&gt;=2) a) <br>--这是一天有两条或以上出差记录的情况<br><br>另外：你说<br>一个多小时过去了,连一个回复的都没有,有点失望.<br>请注意，并不是每人的作息时间都跟你是一致的<br>而且回答问题是助人为乐，但不是别人的义务

几万条记录不算多, 来个千万级才算难, 被骗了!<br>只要做个存储过程, 把结果送过来即可, 若不想<br>使用存储过程可以使用内存表即可!

多人接受答案了。