T-SQL语句的综合应用,生成工资报表

View Code 
 1 ----T-SQL语句的综合应用-----
 2 create table AbsenceType
 3 (
 4 AtpID int primary key identity,
 5 AtpName nvarchar(20) not null,
 6 AtpFine decimal(18,2)not null
 7 )
 8 
 9 insert into AbsenceType(AtpName,AtpFine) values('迟到',20)
10 insert into AbsenceType(AtpName,AtpFine) values('缺勤',100)
11 insert into AbsenceType(AtpName,AtpFine) values('早退',20)
12 
13 create table Absence
14 (
15 AbsGUID int primary key identity,
16 AbsDate datetime not null,
17 FEmpGUID int foreign key references Employee(empGUID),
18 FAtpID int foreign key references AbsenceType(AtpID)
19 )
20 
21 insert into Absence(AbsDate,FEmpGUID,FAtpID)values('2009-1-1',1,1)
22 insert into Absence(AbsDate,FEmpGUID,FAtpID)values('2009-1-5',1,2)
23 insert into Absence(AbsDate,FEmpGUID,FAtpID)values('2009-1-5',2,3)
24 insert into Absence(AbsDate,FEmpGUID,FAtpID)values('2009-1-15',2,1)
25 insert into Absence(AbsDate,FEmpGUID,FAtpID)values('2009-2-1',3,3)
26 
27 
28 Select * from Employee
29 Select * from dbo.EmpSalary
30 Select * from dbo.Absence
31 Select * from dbo.Absence
32 
33 
34 ----生成2009年1月份的工资报表
35 
36 declare @count int ---总员工数
37 select @count=count(*) from Employee
38 declare @i int
39 set @i=1  ----循环初始值
40 declare @money int ----扣钱的变量
41 while(@i<@count) ----循环开始
42 begin   ----每次循环得到一个员工的工号
43 declare @empworknum varchar(10)
44 select @empworknum=empWorkNum from
45     (select row_number() over(order by empGUID) as newEmpID,* from Employee)as newemptable
46     where newemptable.newEmpID=@i
47     
48 set @money=0    ----得到月份的当前员工的当月缺勤罚款
49 select @money=sum(AtpFine) from Absence ab
50 inner join AbsenceType at
51 on ab.FAtpID=at.AtpID
52 inner join Employee em
53 on ab.FEmpGUID=em.EmpGUID
54 Where (month(ab.AbsDate)=1)
55 group by empworknum
56 having empworknum=@empworknum
57 ----将查询结果放入到工资表中
58 insert into EmpSalary(salYear,salMonth,salMoney,FEmpGUID)
59                select 2009,1,EmpSalary-@money,EmpGUID from Employee 
60                where empWorkNum=@empworknum
61 set @i=@i+1  -----控制循环条件 
62 end
posted @ 2011-05-16 16:04  eva.xiao  阅读(885)  评论(1)    收藏  举报