mysql - 如何使用最低工资和最高工资列计算平均工资

Question

详细来说，我有一个职位表，其中包含公司名称、职位、最低工资、最高工资、最低经验、最高经验等。

对于相关职位，我试图给出平均工资和经验。我想列出公司名称(xxx)，其角色数据库开发人员提供平均工资和平均经验。

 
declare user_details cursor FOR
			select up.role,ur.years,ur.month,upd.current_ctc from user_registration ur 
			left join user_personal_details upd on upd.registration_user_id=ur.iduser_registration
			left join user_projects up on up.employee_id=ur.iduser_registration
			where ur.iduser_registration=p_userID;
		open user_details;
		fetch user_details into v_role,v_exp_years,v_exp_months,v_current_ctc;
		#select v_role,v_exp_years,v_exp_months,v_current_ctc;
		set v_experience= concat(COALESCE(v_exp_years,0),'.',COALESCE(v_exp_months,0));
		#select v_experience;
			BLOCK2: BEGIN
				declare jobs_list cursor for
					select jobid,cr.company_name,jp.max_salary from job_posts jp
					join client_registration cr on cr.idclient_registration=jp.idclient_registration
					where jp.designation=v_role
					and v_experience between jp.min_experience and jp.max_experience  
					group by jp.jobid order by jp.createdon limit 5;

				DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
					OPEN jobs_list;
					set v_result_joblist='';
					set v_result_role_list='';
					jobs_list_loop: LOOP
					FETCH FROM jobs_list INTO v_jobID,v_company_name,v_max_salary;

						IF v_finished=1 THEN LEAVE jobs_list_loop;        END IF;
						set  v_result_joblist=concat(v_result_joblist,v_jobID,'-',v_company_name,'-',v_max_salary,',');
	
							if(v_result_role_list is null or v_result_role_list='') then 
								set v_result_role_list= v_company_name;
							else                        
								set v_result_role_list=concat_ws('-',v_result_role_list,v_company_name);
							end if;
								BLOCK3: BEGIN
									declare role_wise_jobposts cursor for
										select jp.designation,max(jp.max_salary),min(jp.min_salary),max(jp.max_experience),min(jp.min_experience) from job_posts jp
										join client_registration cr on cr.idclient_registration=jp.idclient_registration
										where cr.company_name=v_company_name
										group by jp.designation order by jp.createdon;

										DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finish = 1;

										OPEN role_wise_jobposts;
											role_wise_joblist: loop
											FETCH FROM role_wise_jobposts INTO v_role_list,v_max_salary,v_min_salary,v_max_exp,v_min_exp;
											 				
											IF v_finish=1 THEN                LEAVE role_wise_joblist;              END IF;
											set v_result_role_list=concat_ws(',',v_result_role_list,concat('_',v_role_list),v_max_salary,v_min_salary,v_max_exp,v_min_exp);
										END LOOP role_wise_joblist;
											set v_finish=0;
											set v_result_role_list=concat_ws('-',v_result_role_list);
										CLOSE role_wise_jobposts;
											#select v_result_role_list;
								END BLOCK3;

							#select v_result_role_list;
				END LOOP jobs_list_loop;

			END BLOCK2;
	close user_details;
	select v_result_joblist UNION select v_result_role_list;
END

Answer 1

进行如下查询:

SELECT avg(col1 + col2)
FROM test
WHERE uid=5;

SQL Fiddle Demo