select c.員工ID,b.name 姓名,d.部門,month(a.年) 月, SUM(a.基本工资) 基本工资,sum(a.扣出) 扣出,
sum(a.奖励) 奖励, SUM(a.基本工资)-sum(a.扣出)+sum(a.奖励) 实际发放,
sum(b.年薪) 年薪总和,
sum(b.年薪)-SUM(a.基本工资)+sum(a.扣出)-sum(a.奖励) 剩余年薪
from 表2 a inner join 表1 b on b.姓名 = a.姓名
inner join c on b.姓名 = c.姓名 inner join d on d.部門編號 = c.部門編號
where year(a.年)='2003' group by month(a.年),c.員工ID,b.name 姓名,d.部門
union
select '合计',sum(基本工资),sum(a.扣出),sum(a.奖励),
SUM(a.基本工资)-sum(a.扣出)+sum(a.奖励),sum(b.年薪),
sum(b.年薪)-SUM(a.基本工资)+sum(a.扣出)-sum(a.奖励)
from 表2 a inner join 表1 b on b.姓名 = a.姓名
inner join c on b.姓名 = c.姓名 inner join d on d.部門編號 = c.部門編號
where year(a.年)='2003' group by month(a.年),c.員工ID,b.name 姓名,d.部門
where year(a.年)='2003'
sum(a.奖励) 奖励, SUM(a.基本工资)-sum(a.扣出)+sum(a.奖励) 实际发放,
sum(b.年薪) 年薪总和,
sum(b.年薪)-SUM(a.基本工资)+sum(a.扣出)-sum(a.奖励) 剩余年薪
from 表2 a inner join 表1 b on b.姓名 = a.姓名
inner join c on b.姓名 = c.姓名 inner join d on d.部門編號 = c.部門編號
where year(a.年)='2003' group by month(a.年),c.員工ID,b.name 姓名,d.部門
union
select '合计',sum(基本工资),sum(a.扣出),sum(a.奖励),
SUM(a.基本工资)-sum(a.扣出)+sum(a.奖励),sum(b.年薪),
sum(b.年薪)-SUM(a.基本工资)+sum(a.扣出)-sum(a.奖励)
from 表2 a inner join 表1 b on b.姓名 = a.姓名
inner join c on b.姓名 = c.姓名 inner join d on d.部門編號 = c.部門編號
where year(a.年)='2003' group by month(a.年),c.員工ID,b.name 姓名,d.部門
where year(a.年)='2003'
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货