一定要用存储过程实现
参数:日期,日期Employee 表SystemID JobID EmpName DeptID00001 B0001 张华0 D001
00002 B0002 张华1 D005
00003 B0003 张华2 D003
00004 B0004 张华3 D002
00005 B0005 张华4 D001
00006 B0006 张华5 D004
00007 B0007 张华6 D002
00008 B0008 张华7 D003
00009 B0009 张华8 D006DepartMent 表DeptID DeptName D001 工程部
D002 生产部
D003 后勤部
D004 财备部
D005 行政部
D006 业务部KqCollectDay表
SystemID KqDate AttendHours
0001 2008-12-1 7.5
0001 2008-12-2 7.5
0001 2008-12-3 7.5
0001 2008-12-4 7.5
0002 2008-12-1 7.5
0003 2008-12-2 7.5
0003 2008-12-3 7.5
0004 2008-12-5 7.5
0005 2008-12-1 7.5
0006 2008-12-2 7.5
0003 2008-12-1 7.5
0004 2008-12-1 7.5
根据日期,组别把得到的数据插入一个已经存在的表里KqTime表
KqTime表
SystemID JobID EmpName DeptName KqDate AttendHours
00001 B0001 张华0 工程部 2008-12-1 7.5
00005 B0005 张华4 工程部 2008-12-1 7.5
参数:日期,日期Employee 表SystemID JobID EmpName DeptID00001 B0001 张华0 D001
00002 B0002 张华1 D005
00003 B0003 张华2 D003
00004 B0004 张华3 D002
00005 B0005 张华4 D001
00006 B0006 张华5 D004
00007 B0007 张华6 D002
00008 B0008 张华7 D003
00009 B0009 张华8 D006DepartMent 表DeptID DeptName D001 工程部
D002 生产部
D003 后勤部
D004 财备部
D005 行政部
D006 业务部KqCollectDay表
SystemID KqDate AttendHours
0001 2008-12-1 7.5
0001 2008-12-2 7.5
0001 2008-12-3 7.5
0001 2008-12-4 7.5
0002 2008-12-1 7.5
0003 2008-12-2 7.5
0003 2008-12-3 7.5
0004 2008-12-5 7.5
0005 2008-12-1 7.5
0006 2008-12-2 7.5
0003 2008-12-1 7.5
0004 2008-12-1 7.5
根据日期,组别把得到的数据插入一个已经存在的表里KqTime表
KqTime表
SystemID JobID EmpName DeptName KqDate AttendHours
00001 B0001 张华0 工程部 2008-12-1 7.5
00005 B0005 张华4 工程部 2008-12-1 7.5
as
insert KqTime(SystemID,JobID,EmpName,DeptName,KqDate,AttendHours)
select
a.SystemID,a.JobID,a.EmpName,b.DeptName,c.KqDate,c.AttendHours
from Employee a
inner join DepartMent b on a.DeptI=b.DeptID
inner join KqCollectDay c on c.SystemID=a.SystemID
where c.KqDate=@KqDate
楼主的数据有误,第三个表的SystemID列差一个0
create table Employee(SystemID varchar(10),JobID varchar(10),EmpName nvarchar(10),DeptID varchar(10))
insert into Employee select '00001','B0001','张华0','D001'
insert into Employee select '00002','B0002','张华1','D005'
insert into Employee select '00003','B0003','张华2','D003'
insert into Employee select '00004','B0004','张华3','D002'
insert into Employee select '00005','B0005','张华4','D001'
insert into Employee select '00006','B0006','张华5','D004'
insert into Employee select '00007','B0007','张华6','D002'
insert into Employee select '00008','B0008','张华7','D003'
insert into Employee select '00009','B0009','张华8','D006' create table DepartMent(DeptID varchar(10),DeptName nvarchar(10))
insert into DepartMent select 'D001','工程部'
insert into DepartMent select 'D002','生产部'
insert into DepartMent select 'D003','后勤部'
insert into DepartMent select 'D004','财备部'
insert into DepartMent select 'D005','行政部'
insert into DepartMent select 'D006','业务部' create table KqCollectDay(SystemID varchar(10),KqDate datetime,AttendHours decimal(5,2))
insert into KqCollectDay select '00001','2008-12-1',7.5
insert into KqCollectDay select '00001','2008-12-2',7.5
insert into KqCollectDay select '00001','2008-12-3',7.5
insert into KqCollectDay select '00001','2008-12-4',7.5
insert into KqCollectDay select '00002','2008-12-1',7.5
insert into KqCollectDay select '00003','2008-12-2',7.5
insert into KqCollectDay select '00003','2008-12-3',7.5
insert into KqCollectDay select '00004','2008-12-5',7.5
insert into KqCollectDay select '00005','2008-12-1',7.5
insert into KqCollectDay select '00006','2008-12-2',7.5
insert into KqCollectDay select '00003','2008-12-1',7.5
insert into KqCollectDay select '00004','2008-12-1',7.5 create table KqTime(SystemID varchar(10),JobID varchar(10),EmpName nvarchar(10),DeptName nvarchar(10),KqDate datetime,AttendHours decimal(5,2))
go
CREATE PROCEDURE my_proc (@KqDate datetime,@DeptID varchar(10))
AS
insert into KqTime
select a.SystemID , a.JobID , a.EmpName , b.DeptName , @KqDate , c.AttendHours
from Employee a inner join DepartMent b on a.DeptID = b.DeptID
inner join KqCollectDay c on a.SystemID = c.SystemID
where c.KqDate=@KqDate and b.DeptID=@DeptID
go
exec my_proc '2008-12-1','D001'
select * from KqTime
go
drop table Employee,KqCollectDay,DepartMent,KqTime
drop procedure my_proc
/*
SystemID JobID EmpName DeptName KqDate AttendHours
---------- ---------- ---------- ---------- ----------------------- ---------------------------------------
00001 B0001 张华0 工程部 2008-12-01 00:00:00.000 7.50
00005 B0005 张华4 工程部 2008-12-01 00:00:00.000 7.50(2 行受影响)
*/