求一高难度SQL语句,请各位高手进来看看,帮帮我!(100分)

T

taibu

Unregistered / Unconfirmed
GUEST, unregistred user!
有以下三个表<br>Table1(保存所有班次时间)<br>Autoid &nbsp; &nbsp;StartTime &nbsp; &nbsp; EndTime<br>1 &nbsp; &nbsp; &nbsp; &nbsp; 07:30 &nbsp; &nbsp; &nbsp; &nbsp; 19:30<br>2 &nbsp; &nbsp; &nbsp; &nbsp; 08:00 &nbsp; &nbsp; &nbsp; &nbsp; 20:00<br><br>Table2(所有员工每天对应的班次ID,Employeeid为每次员工对应员工表Employee的ID号)<br>Autoid &nbsp; Employeeid &nbsp; CurDate &nbsp; &nbsp;ClassId<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-01 &nbsp; 1<br>2 &nbsp; &nbsp; &nbsp; &nbsp;24 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-01 &nbsp; 2<br>3 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 &nbsp; 2<br>4 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-03 &nbsp; 1<br>5 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-04 &nbsp; 2<br>6 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-05 &nbsp; 2<br>7 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 &nbsp; 1<br>8 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-10 &nbsp; 2<br><br>Table3(保存所有员工的出差记录)<br>Autoid &nbsp; Employeeid &nbsp; &nbsp; &nbsp; StartTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EndTime<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-01-28 08:00:00 &nbsp; &nbsp; 2008-02-10 &nbsp;08:00:00<br>2 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 08:00:00 &nbsp; &nbsp; 2008-02-02 &nbsp;20:00:00<br>3 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 10:30:00 &nbsp; &nbsp; 2008-02-02 &nbsp;14:00:00<br><br>我想通过以上三个表,得出每个员工每天的出差开始时间与结束时间.<br>例如,我得出2008-02-01到2008-02-03之间每个员工每天的出差时间明细,如下:<br>Table4(所求表,)<br>Employeeid &nbsp; &nbsp; CurDate &nbsp; &nbsp; StartTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EndTime<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-01 &nbsp;07:30:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19:30:00<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 &nbsp;08:00:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20:00:00<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-03 &nbsp;07:30:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19:30:00<br>30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 &nbsp;10:30:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14:00:00<br><br>请各位高手帮帮出出主意,如何才能以最快速度得出TABLE4呢?
 
select<br>&nbsp; b.Employeeid,<br>&nbsp; b.CurDate,<br>&nbsp; a.StartTime,<br>&nbsp; a.EndTime<br>from table1 a,table2 b,table3 c<br>where<br>--刚想写,发现问得有问题,拉倒
 
to 月满C楼<br>请问,怎么问得有问题?不够客气吗?还是.....
 
select T2.EmployeeID, T2.CurrDate, T1.StartTime, T1.EndTime<br>from Table2 T2<br>&nbsp; left join Table1 T1 on T2.ClassID = T1.AutoID<br>where T2.CurrDate between '20080201' and '20080203'<br><br>Table3没啥必要,Tabel2已经记录了明细了,Table3还再记录一次汇总做啥?
 
to QQ在线<br>你可能没明白我的意思,Table2只是记录的每天的班次,而并不是每天出差的开始时间,每天的开始时间还要根据班次开始时间及结束时间及Table3来计算.假如某人在2008-02-02的班次ID为1,而该人有一出差记录为2008-01-28 08:00:00至2008-02-02 15:00:00,则该人在2008-02-02的出差时间为:08:00~15:00(班次时间规定为08:00~20:00,而15:00:00 &lt; 20:00:00,假如该天的班次ID为07:30:00~19:30:00,则出差时间为07:30:00~15:00)
 
用触发吧,在table3插入记录的时候产生table4的记录<br>比较慢的方法是在触发中比较出差开始结束的日期,超过一天需要一天天产生table4中的记录
 
问题仍没解决,还需要各位帮助!
 
问题搞的太过复杂,保留点脑细胞,呵呵
 
分解用临时表做吧<br>一条句,也可能,就是<br><br>exec kq_proc<br><br>用存储过程啦 ,哈哈
 
给你个思路:<br>1.先由Table1,Table2写第一个视图,给出每人每天的上下班时间;<br>2.再由Table2,Table3写第二个视图,给出每人每天的出差时间;<br>3.结合前两个视图,得出Table4其实也是一个视图,不是Table
 
有时间,我看看,现在忙.
 
看看是不是需要这样.没有太明白你的需求<br>select table2.employeeid ,table2.curdate,table1.starttime,table1.endtime from table2 left join table3 on table2.employeeid =table3.employeeid left join table1 on table2.classid =table1.autoid where <br>(curdate &gt;=2008-02-01)and(curdate&lt;=2008-02-03)
 
考勤啊,嘿[:D]
 
这个很好处理,但是涉及公司的技术问题,难啊[:(]
 
两个贴,写那个好啊
 
我看了好大一会儿,看不懂你在说那样?请重新好好的将问题说清楚
 
有这样设计数据库的。。。。。。。。。。<br>思路都不清晰,<br>Table3(保存所有员工的出差记录)<br>Autoid &nbsp; Employeeid &nbsp; &nbsp; &nbsp; StartTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EndTime<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-01-28 08:00:00 &nbsp; &nbsp; 2008-02-10 &nbsp;08:00:00<br>2 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 08:00:00 &nbsp; &nbsp; 2008-02-02 &nbsp;20:00:00<br>3 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 10:30:00 &nbsp; &nbsp; 2008-02-02 &nbsp;14:00:00<br><br>我想通过以上三个表,得出每个员工每天的出差开始时间与结束时间.<br>例如,我得出2008-02-01到2008-02-03之间每个员工每天的出差时间明细,如下:<br>Table4(所求表,)<br>Employeeid &nbsp; &nbsp; CurDate &nbsp; &nbsp; StartTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EndTime<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-01 &nbsp;07:30:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19:30:00<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 &nbsp;08:00:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20:00:00<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-03 &nbsp;07:30:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19:30:00<br>30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-02-02 &nbsp;10:30:00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14:00:00<br><br>Table3 &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-01-28 08:00:00 &nbsp; &nbsp; 2008-02-10 &nbsp;08:00:00<br>都显示是08:00:00 楼主还能算成是07:30:00<br>那table3的时间还有什么意义,直接根据出差日期和班次时间来算就可以了
 
一看表设计,然后一开要求的结果集,我晕了一下
 
to 々飞翔鸟々<br>我想,你可能还没明白我的几个表中,每个表的作用.<br>首先.Table3表每条记录保存的每天的出差时间范围,而可能是好几天的记录,因为可能一个人出差一次就是半个月或更长时间,如果一天一条记录的话,那员工多的话,不是记录很多?<br>Table1表,规定每个班次的开始时间与结束时间,Table2表则是每个员工每天的班次,由Table1及Table2两个表,才能知道每个人每天的上班时间与下班时间.<br>知道了每个人每天的上班时间与下班时间后,再结束Table3表,假如某人某天有出差记录的话,则才能算出其具体出差小时数.<br>问题是有点复杂,但我自己已找到解决方法.谢谢各位了!
 
请以上参与过回答问题的人员到下面拿分!<br>http://www.delphibbs.com/delphibbs/dispq.asp?lid=3895279
 

Similar threads

顶部