表A
--------------------------
Employee_sn StartDate EndDate
1 '2007-01-02 ' '2007-01-03 '
1 '2007-01-05 ' '2007-01-06 '
1 '2007-01-09 ' '2007-01-13 '
2 '2007-02-02 ' '2007-02-03 '
3 '2007-04-02 ' '2007-04-03 '
表B
---------------------------
Employee_sn CreateDate
1 '2007-01-02 '
1 '2007-01-05 '
1 '2007-01-03 '
3 '2007-01-02 '
1 '2007-01-04 '
1 '2007-01-02 '
2 '2007-01-02 '
表C
--------------------------
Employee_sn StartDate EndDate Count
1 '2007-01-02 ' '2007-01-03 ' 3
1 '2007-01-05 ' '2007-01-06 ' 1
1 '2007-01-09 ' '2007-01-13 ' 0
2 '2007-02-02 ' '2007-02-03 ' 1
3 '2007-04-02 ' '2007-04-03 ' 1
---------------------
就是根据表A中的时间段和员工编号到表B中去统计出对应的员工在该时间段的记录总数.
表A中的员工可能会有多个时间段,但时间段彼此不会重复.
---------
在线等待...
------解决方案--------------------
select Employee_sn,StartDate,EndDate,(select sum(1) from B where B.employee_sn=A.employee_sn and B.createdate between StartDate and EndDate) as Count from A