求教一条合并两个数据表的查询语句,详情如下:

[sun20031223]

考勤周次定义表Weekly
Week_num varchar(6),----周次
WStart DateTime,----开始日期
WEnd DateTime,----结束日期

200601 2006-01-01 2006-01-05
200602 2006-01-06 2006-01-12

考勤结果表DailyAttend
EM_ID Varchar(6),----人员编号
ADate DateTime, ----出勤日期
ATimes Numeric(9,2),----出勤时间

因为要按周分析员工的出勤时间，我想对考勤结果表DailyAttend的日期在周次表Weekly的定义范围内的周次,合成一个新的记录集，即：
Week_Num ADate EM_ID ATimes
200601 2006-01-01 001 9.0
200601 2006-01-02 001 8.0
200601 2006-01-03 001 9.5
200601 2006-01-04 001 8.5
200601 2006-01-05 001 9.0
200601 2006-01-01 002 9.0
200601 2006-01-02 002 9.0
200601 2006-01-03 002 9.0
200601 2006-01-04 002 9.0
200602 2006-01-06 001 9.0
200602 2006-01-07 001 9.0

[sun20031223]

To:lzhs(快乐至上),

同行!有同感哦!
我专做项目的,还做MRPII,ERP等,交个朋友好吗?我在广州,[e-mail]

[lzhs]

呵呵．
可以啊．我的ＭＳＮ：[e-mail]
QQ:25919547

:)

[lzhs]

declare @Weekly table(Week_num varchar(6),WStart DateTime,WEnd DateTime)
insert into @Weekly values('200601','2006-01-01','2006-01-05')
insert into @Weekly values('200602','2006-01-06','2006-01-12')

declare @DailyAttend table(EM_ID varchar(6),ADate DateTime,ATimes Numeric(9,2))
insert into @DailyAttend values(1,'2006-01-01','9.0')
insert into @DailyAttend values(1,'2006-01-02','8.0')
insert into @DailyAttend values(1,'2006-01-03','9.5')
insert into @DailyAttend values(1,'2006-01-04','8.5')
insert into @DailyAttend values(1,'2006-01-05','9.0')
insert into @DailyAttend values(1,'2006-01-06','9.0')
insert into @DailyAttend values(1,'2006-01-07','9.0')
insert into @DailyAttend values(2,'2006-01-01','9.0')
insert into @DailyAttend values(2,'2006-01-02','9.0')
insert into @DailyAttend values(2,'2006-01-03','9.0')
insert into @DailyAttend values(2,'2006-01-04','9.0')

Select w.Week_num,ADate,EM_ID,ATimes
From @Weekly w,@DailyAttend d
Where d.ADate Between w.WStart And w.WEnd
Order By Week_num,ADate

[lzhs]

楼主，顺便说一下，我也是做人事系统的．
:)

考勤这一块太复杂了～～～

[tangjingjie]

两 个表没有一个关联的字段吗？那怎么知道哪条记录该和哪条记录在一起呀？