有两个表
1、Employee
EmployeeID char(6) --编号(主键)
UserName char(10) --姓名
Sex char(2) --性别
Birthday datetime --出生日期
Station char(10) --用来标识人员的身份(取值: 干部、工人)
2、PostAppraise
AssessID int --记录号(主键自增)
EmployeeID char(6) --编号(外码)
ApplyName char(10) --职称(取值:科级、副科级、处级、副处级、教授、副教授、其他)
要求:
查询出满足以下要求人员的姓名,
如果是男性:干部且年龄大于60,工人且年龄大于55;
如果是女性:干部且年龄大于55,工人且年龄大于50,另外如果该女性获得处级或副教授以上职称则年龄大于60
1、Employee
EmployeeID char(6) --编号(主键)
UserName char(10) --姓名
Sex char(2) --性别
Birthday datetime --出生日期
Station char(10) --用来标识人员的身份(取值: 干部、工人)
2、PostAppraise
AssessID int --记录号(主键自增)
EmployeeID char(6) --编号(外码)
ApplyName char(10) --职称(取值:科级、副科级、处级、副处级、教授、副教授、其他)
要求:
查询出满足以下要求人员的姓名,
如果是男性:干部且年龄大于60,工人且年龄大于55;
如果是女性:干部且年龄大于55,工人且年龄大于50,另外如果该女性获得处级或副教授以上职称则年龄大于60
--try:select username from employee a ,
postappraise b
where a.EmployeeID=b.EmployeeID and
(sex='男' and station='干部' and 年龄>60)
or (sex='男' and station='工人' and 年龄>55)
or (sex='女' and station='干部' and 年龄>55)
or (sex='女' and station='工人' and 年龄>50)
or (sex='女' and applyname in('处级','副教授') and 年龄>60)
select username from employee a ,
postappraise b
where a.EmployeeID=b.EmployeeID and
((sex='男' and station='干部' and datediff(yy,Birthday,getdate())>60)
or (sex='男' and station='工人' and datediff(yy,Birthday,getdate())>55)
or (sex='女' and station='干部' and datediff(yy,Birthday,getdate())>55)
or (sex='女' and station='工人' and datediff(yy,Birthday,getdate())>50)
or (sex='女' and applyname in('处级','副教授') and datediff(yy,Birthday,getdate())>60))
Select e.*,p.*
from Employee e Inner join PostAppraise p on e.EmployeeID=p.EmployeeID
where datediff(year,e.birthday,getdate())> case when Station='干部' and Sex='男'then 60 when Station='工人' and Sex='男' then 55
when Station='干部' and Sex='女'then 55 when Station='工人' and Sex='女' then 50
end
where m.EmployeeID = n.EmployeeID and
((m.Sex = '男' and m.Station '干部' and datediff(year,Birthday,getdate()) > 60) or
(m.Sex = '男' and m.Station '工人' and datediff(year,Birthday,getdate()) > 55) or
(m.Sex = '女' and (n.ApplyName = '处级' or n.ApplyName = '副教授' or n.ApplyName = '教授') and datediff(year,Birthday,getdate()) > 60) or
(m.Sex = '女' and m.Station '干部' and n.ApplyName <> '处级' and n.ApplyName <> '副教授' and n.ApplyName <> '教授' and datediff(year,Birthday,getdate()) > 55) or
(m.Sex = '女' and m.Station '工人' and datediff(year,Birthday,getdate()) > 50))