有2张表,salary (Pk_salary , presonID ,Ddate ,Amount ,Bonus ) ,person ( presonID ,deptID ,Name,sex, age)
要做这样一张表:
年||男员工合计工资 ||男员工平均工资||女员工合计工资||女员工平均工资
请求帮助~
工资表(流水号,工号,发工资日期,基本工资,奖金)
人员表(工号,部门号,姓名,性别,年龄)
orcale环境
按全年男女合计工资与平均工资
答案:3 悬赏:50
解决时间 2021-11-27 22:57
- 提问者网友:西路不相离
- 2021-11-27 07:13
最佳答案
- 二级知识专家网友:何以畏孤独
- 2021-11-27 07:28
语法可以过,但我没环境,自己测试着改改
select Man.[年],Man.[男员工合计工资],Man.[男员工平均工资],WoMan.[女员工合计工资],WoMan.[女员工平均工资] from
(select Substring(Ddate,1,4) as '年',sum(Amount+Bonus) as '男员工合计工资',avg(Amount+Bonus) as '男员工平均工资'
from person left join salary on person.presonID=salary.presonID where sex='男' group by Substring(Ddate,1,4)) as Man
inner join
(select Substring(Ddate,1,4) as '年',sum(Amount+Bonus) as '女员工合计工资',avg(Amount+Bonus) as '女员工平均工资'
from person left join salary on person.presonID=salary.presonID where sex='女' group by Substring(Ddate,1,4)) as WoMan
on Man.[年]=WoMan.[年]
select Man.[年],Man.[男员工合计工资],Man.[男员工平均工资],WoMan.[女员工合计工资],WoMan.[女员工平均工资] from
(select Substring(Ddate,1,4) as '年',sum(Amount+Bonus) as '男员工合计工资',avg(Amount+Bonus) as '男员工平均工资'
from person left join salary on person.presonID=salary.presonID where sex='男' group by Substring(Ddate,1,4)) as Man
inner join
(select Substring(Ddate,1,4) as '年',sum(Amount+Bonus) as '女员工合计工资',avg(Amount+Bonus) as '女员工平均工资'
from person left join salary on person.presonID=salary.presonID where sex='女' group by Substring(Ddate,1,4)) as WoMan
on Man.[年]=WoMan.[年]
全部回答
- 1楼网友:初心未变
- 2021-11-27 08:53
用中文把表写出来呗 应该不难 我就是不会英语
- 2楼网友:星星坠落
- 2021-11-27 08:43
用中文把表写出来呗 应该不难 我就是不会英语
再看看别人怎么说的。
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯