mysql多表关联、sum、GROUP BY函数使用

最新推荐文章于 2024-07-26 03:39:13 发布

不习惯i

最新推荐文章于 2024-07-26 03:39:13 发布

阅读量590

点赞数 7

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_45386594/article/details/135158208

版权

SELECT
  '01在职' 状态,
	a.A0100,
	a.A0191,
	a.A01BE,
-- 	any_value(a.A0191),
-- 	any_value(a.A01BE),
	a.A0101,
	any_value(a.E01A1),
	any_value(a.A0141),
	any_value(a.A0142),
	any_value(a.A01BN),
 	sum(b.A58DB+b.A58AB+b.A5873+b.A58BN+b.A58CA) as 基岗工资,
	sum(b.A58BE) as 预发奖金,
  sum(b.A5870) as 已发薪资（合计）,
	sum(b.A58DB+b.A58AB) as 基岗工资1,
	sum(b.A58BC+f.A0GAK+f.A0GAL+f.A0GAM) as 应发奖金,
	sum(b.A58AD) as 应发年薪（合计）,
	sum(b.A58CF) as 驻外补贴（月度）,
	sum(f.A0GAN) as 驻外补贴（年度）,
	sum(b.A58CM) as 特殊补贴,
	sum(b.A58BM) as 考核系数,
	( CASE WHEN a.A01BE <> '04' and a.A01BE <> '05' THEN sum(b.A58BC+f.A0GAK+f.A0GAL+f.A0GAM)/sum(b.A58BM) ELSE '' END ) AS 核定奖金,
	( CASE WHEN a.A01BE <> '04' and a.A01BE <> '05' THEN sum(b.A58BC+f.A0GAK+f.A0GAL+f.A0GAM)/sum(b.A58BM) ELSE '' END ) AS 核定年薪,
-- 	''as 核定奖金,
-- 	''as 核定年薪,
	''as 工作月数,
	''as 核定年薪（月折算）,
	''as 应发年薪（月折算）,
	any_value(d.A55AN) as 最新基本工资,
	any_value(d.A5530) as 最新岗位工资,
	any_value(d.A55BB) as 最新预发奖金,
	any_value(d.A55AS) as 最新薪等,
	any_value(d.A55AR) as 最新薪级,
	any_value(b.A58CJ) as 最新应扣事假工资,
	any_value(b.A58AY) as 最新税前其他扣款,
	any_value(b.A58CK) as 最新应扣病假工资,
	any_value(b.A58CL) as 最新应扣事（病）假奖金,
	any_value(b.A58CG) as 最新应扣驻外补贴,
	any_value(A58Z0) as fxsj
-- 	any_value(DATE_FORMAT(b.A58Z0,'%m') yf)
--   DATE_FORMAT(b.A58Z0,'%Y') nf,	 -- 年度
-- 	DATE_FORMAT(b.A58Z0,'%m') yf  -- 期间
FROM
	usra01 a
	LEFT JOIN ( SELECT a0100, max(I9999) as i99991 FROM usra55 GROUP BY a0100 ) AS c ON a.a0100 = c.a0100
	LEFT JOIN usra55 d on d.a0100=c.a0100 and  c.i99991=d.I9999
	LEFT JOIN ( SELECT a0100, max(I9999) as i99992 FROM usra58 GROUP BY a0100 ) AS e ON a.a0100 = e.a0100
	LEFT JOIN usra58 b ON b.A0100 = e.A0100 and  e.i99992=b.I9999
	LEFT JOIN (select f1.A0100,f1.A0GAK,f1.A0GAL,f1.A0GAM,f1.A0GAN from usra0g f1,usra58 f2 where f1.A0100=f2.A0100 and f2.A58DW LIKE 'Z60%' ) f ON a.A0100 = f.A0100 
where a.B0110 LIKE 'Z60%'   or a.A0144='19920003'
GROUP BY a.a0100,a.a0101,a.A0191,a.A01BE
union
SELECT
  '02离职' 状态,
	a.A0100,
	a.A0191,
	a.A01BE,
-- 	any_value(a.A0191),
-- 	any_value(a.A01BE),
	a.A0101,
	any_value(a.E01A1),
	any_value(a.A0141),
	any_value(a.A0142),
	any_value(a.A01BN),
 	sum(b.A58DB+b.A58AB+b.A5873+b.A58BN+b.A58CA) as 基岗工资,
	sum(b.A58BE) as 预发奖金,
  sum(b.A5870) as 已发薪资（合计）,
	sum(b.A58DB+b.A58AB) as 基岗工资1,
	sum(b.A58BC+f.A0GAK+f.A0GAL+f.A0GAM) as 应发奖金,
	sum(b.A58AD) as 应发年薪（合计）,
	sum(b.A58CF) as 驻外补贴（月度）,
	sum(f.A0GAN) as 驻外补贴（年度）,
	sum(b.A58CM) as 特殊补贴,
	sum(b.A58BM) as 考核系数,
	( CASE WHEN a.A01BE <> '04' and a.A01BE <> '05' THEN sum(b.A58BC+f.A0GAK+f.A0GAL+f.A0GAM)/sum(b.A58BM) ELSE '' END ) AS 核定奖金,
	( CASE WHEN a.A01BE <> '04' and a.A01BE <> '05' THEN sum(b.A58BC+f.A0GAK+f.A0GAL+f.A0GAM)/sum(b.A58BM) ELSE '' END ) AS 核定年薪,
-- 	''as 核定奖金,
-- 	''as 核定年薪,
	''as 工作月数,
	''as 核定年薪（月折算）,
	''as 应发年薪（月折算）,
	any_value(d.A55AN) as 最新基本工资,
	any_value(d.A5530) as 最新岗位工资,
	any_value(d.A55BB) as 最新预发奖金,
	any_value(d.A55AS) as 最新薪等,
	any_value(d.A55AR) as 最新薪级,
	any_value(b.A58CJ) as 最新应扣事假工资,
	any_value(b.A58AY) as 最新税前其他扣款,
	any_value(b.A58CK) as 最新应扣病假工资,
	any_value(b.A58CL) as 最新应扣事（病）假奖金,
	any_value(b.A58CG) as 最新应扣驻外补贴,
	any_value(A58Z0) as fxsj
-- 	any_value(DATE_FORMAT(b.A58Z0,'%m') yf)
--   DATE_FORMAT(b.A58Z0,'%Y') nf,	 -- 年度
-- 	DATE_FORMAT(b.A58Z0,'%m') yf  -- 期间
FROM
	reta01 a
	LEFT JOIN ( SELECT a0100, max(I9999) as i99991 FROM reta55 GROUP BY a0100 ) AS c ON a.a0100 = c.a0100
	LEFT JOIN reta55 d on d.a0100=c.a0100 and  c.i99991=d.I9999
	LEFT JOIN ( SELECT a0100, max(I9999) as i99992 FROM reta58 GROUP BY a0100 ) AS e ON a.a0100 = e.a0100
	LEFT JOIN reta58 b ON b.A0100 = e.A0100 and  e.i99992=b.I9999
	LEFT JOIN (select f1.A0100,f1.A0GAK,f1.A0GAL,f1.A0GAM,f1.A0GAN from reta0g f1,reta58 f2 where f1.A0100=f2.A0100 and f2.A58DW LIKE 'Z60%' ) f ON a.A0100 = f.A0100 
where a.B0110 LIKE 'Z60%' 
GROUP BY a.a0100,a.a0101,a.A0191,a.A01BE