SQL知识运用笔记1
佛山出SQL例子 `-- 呼出 老员工 ` `-- 1、信息表 （取出状态表和数据表关联 状态为1的，成功数据）` drop table if EXISTS temp_info; CREATE table temp_info SELECT a.operating_center, a.date, a.name, a.workdays_1, a.workdays_2, a.workdays_3, a.job_number, a.id_card_no, a.wage_card, a.bank_info, a.out_company_info, `-- 这一步是因为系统有些空值，开发人员默认为了其他值，使用case when then 判断` (CASE when a.out_company_fee!=-999999 then a.out_company_fee else 0 END) out_company_fee, a.employee_attribute, a.mount_guard_date, a.departure_date, a.departure_flag, a.phone_no, (CASE when a.seniority_pay!=-999999 then a.seniority_pay else 0 END) seniority_pay, (CASE when a.award_punish!=-999999 then a.award_punish else 0 END) award_punish, (CASE when a.cash_award_punish!=-999999 then a.cash_award_punish else 0 END) cash_award_punish, (CASE when a.attendance_bouns != -999999 then a.attendance_bouns else 0 END) attendance_bouns, a.deduction, a.base from `-- 导入的数据表` callout_employee_info a, `-- 状态表` callout_employee_info_importbat b where a.batid=b.id and b.status=1 `-- 这个是取时间，2019.02.01要计算2019.01.01-2019.01.31的数据，如果每次改时间会很麻烦， `-- b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 ``-- MONTH),1,7),"%")) 得到的时间是2019-01% SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7)` `-- select CURDATE() ` `-- select DATE_SUB(CURDATE(),INTERVAL 1 MONTH)` `-- INTERVAL 时间间隔，那个填1 以这个月为标准取上个月` and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) and a.operating_center='佛山运营中心' and a.employee_attribute='老员工'; `-- 现金奖罚 信息表的现金奖罚来之新的一张表` drop table if EXISTS temp_award_punish; CREATE table temp_award_punish select a.operating_center, a.wdate, a.wname, a.id_card_no, SUM( (CASE when a.fee!=-999999 then a.fee else 0 END) ) fee from callout_kfjl_info a, callout_kfjl_info_importlog b where a.batid=b.id and b.dstate=1 and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) and operating_center='佛山运营中心' GROUP BY a.id_card_no; `-- 3、社保 游琦姐会发这个月的社保，自己导入` drop table if EXISTS temp_social; CREATE table temp_social select ss_address, ss_name, ss_id_card_no, ss_month, ss_total_personal_benefits from social where ss_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")); `-- 4、`内推奖励` 游琦姐会发这个月的社保，自己导入` drop table if EXISTS temp_interpolate; CREATE table temp_interpolate select i_idcard_of_referrer, SUM(i_write_off_expenses_1+i_write_off_expenses_2+i_write_off_expenses_3+i_write_off_expenses_4+i_write_off_expenses_5) interpolate_total from interpolate WHERE i_data_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) GROUP BY i_idcard_of_referrer; `-- 匹配奖励及扣罚 社保` drop table if EXISTS temp_info_fee; CREATE table temp_info_fee SELECT a.operating_center, a.date, a.name, a.workdays_1, a.workdays_2, a.workdays_3, a.job_number, a.id_card_no, a.wage_card, a.bank_info, a.out_company_info, a.out_company_fee, a.employee_attribute, a.mount_guard_date, a.departure_date, a.departure_flag, a.phone_no, a.seniority_pay, a.award_punish, IFNULL(b.fee,0) cash_award_punish, a.attendance_bouns, a.deduction, a.base, IFNULL(c.ss_total_personal_benefits,0) ss_total_personal_benefits, IFNULL(d.interpolate_total,0) interpolate_total from temp_info a LEFT JOIN temp_award_punish b ON a.id_card_no=b.id_card_no left join temp_social c ON a.id_card_no=c.ss_id_card_no left join temp_interpolate d ON a.id_card_no=d.i_idcard_of_referrer; `-- d.i_idcard_of_the_referrer 内推 ss_total_personal_benefits 社保` `-- 2、明细 工作量表` drop table if EXISTS temp_details; CREATE table temp_details SELECT a.operating_center, a.operating_area, a.date, a.project_name, a.deal_service, a.deal_type, a.reward_unit_price, a.reward_percentage, a.mobile_number, a.job_number, a.employee_name, a.subscriber_number, a.wage_level, a.product_num_integral, sum(a.product_num_integral) total from callout_staff_workload_month a, callout_staff_workload_month_importbat b where a.batid=b.id and b.status=1 and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) and a.operating_center='佛山运营中心' GROUP BY a.job_number; `-- 3、信息表关联明细表` DROP TABLE IF EXISTS temp_total; `-- 存储表` CREATE TABLE temp_total select a.operating_center, a.date, a.name, a.workdays_1, a.workdays_2, a.workdays_3, a.job_number, a.id_card_no, a.wage_card, a.bank_info, a.out_company_info, a.out_company_fee, a.employee_attribute, a.mount_guard_date, a.departure_date, a.departure_flag, a.phone_no, a.seniority_pay, a.award_punish, a.cash_award_punish, a.attendance_bouns, a.deduction, a.base, a.ss_total_personal_benefits, a.interpolate_total, b.project_name, b.deal_service, b.deal_type, b.reward_unit_price, b.reward_percentage, b.mobile_number, b.subscriber_number, b.wage_level, b.product_num_integral, b.total, `-- 产值 产值+信息表的产值奖罚` (CASE when (IFNULL(b.total,0)+a.award_punish)>0 then (IFNULL(b.total,0)+a.award_punish) else 0 END) product, `-- 平均产值` (CASE when (IFNULL(b.total,0)+a.award_punish)>0 then (CASE when a.workdays_2>0 then (IFNULL(b.total,0)+a.award_punish) / a.workdays_2 else 0 END) else 0 END) average from temp_info_fee a LEFT JOIN temp_details b ON a.job_number=b.job_number; `-- 4、算出雅安的工资体系` drop table if EXISTS temp_system; CREATE table temp_system SELECT a.wage_level, a.computation_rule, a.production_num_min, a.production_num_max, a.cardinal_number, a.draw_a_percentage, a.base_pay, a.quantity_award, a.changes_of_subsidies, a.keep_low_wage from callout_wage_system a, callout_wage_system_importlog b where a.batid=b.id and b.dstate=1 and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),0,7),"%")) and a.wage_level='佛山运营中心-老员工'; `-- 5、通过明细表的总产值和信息表产值奖罚，算出每个员工的月总提成` DROP TABLE IF EXISTS temp_commission; `-- 存储表` CREATE TABLE temp_commission select c.operating_center, c.date, c.name, c.workdays_1, c.workdays_2, c.workdays_3, c.job_number, c.id_card_no, c.wage_card, c.bank_info, c.out_company_info, c.out_company_fee, c.employee_attribute, c.mount_guard_date, c.departure_date, c.departure_flag, c.phone_no, c.seniority_pay, c.award_punish, c.cash_award_punish, c.attendance_bouns, c.deduction, c.base, c.project_name, c.deal_service, c.deal_type, c.reward_unit_price, c.reward_percentage, c.mobile_number, c.subscriber_number, c.product_num_integral, c.product, c.total, c.average, d.wage_level, d.computation_rule, d.production_num_min, d.production_num_max, d.cardinal_number, d.draw_a_percentage, d.base_pay, d.quantity_award, d.changes_of_subsidies, d.keep_low_wage, `-- 提成=总产值*提成单价*0.01*提成系数*0.01 (CASE when (c.product-d.cardinal_number)<0 then 0 when (c.product-d.cardinal_number)>0 then (c.product-d.cardinal_number) else 0 END)*d.draw_a_percentage*0.01*c.deduction*0.01 commission_amount, `-- 变动补贴` d.changes_of_subsidies*c.workdays_2 real_change_bonus, `-- 底薪（基础工资）` (CASE when c.workdays_1>0 then d.base_pay/c.workdays_1*(c.workdays_2+c.workdays_3) else 0 END)*0.01*c.base end_base_pay, c.seniority_pay*0 post, c.seniority_pay*0 bank_province, c.seniority_pay*0 bank_cuty, c.seniority_pay*0+100 synthesize, c.seniority_pay*0 connect_long, c.seniority_pay*0 performance_score, `-- 社保` c.ss_total_personal_benefits social_security, `-- 内推` c.interpolate_total restraining, c.seniority_pay*0 the_performance_benefits, c.seniority_pay*0 commission_amount_huru, c.seniority_pay*0 individual_income_tax, c.seniority_pay*0 actual_salary, c.seniority_pay*0 shanghai_mobile_quality_penalty, c.seniority_pay*0 make_up_last_month, c.seniority_pay*0 `make_up_last_month's_salary`, c.seniority_pay*0 cost_division, c.seniority_pay*0 part_time_functions, c.seniority_pay*0 travel_allowance from temp_total c,temp_system d where c.product BETWEEN d.production_num_min and d.production_num_max; -- DROP TABLE IF EXISTS temp_out_salary_1; `-- 存储表` -- CREATE TABLE temp_out_salary_1 `-- 6、工资` drop table if exists temp_out_salary_1; create table temp_out_salary_1 select operating_center '运营中心', post '岗位', name '员工姓名', job_number '员工工号', id_card_no '身份证号', wage_card '工资账号', bank_province '开户行', bank_cuty '开户地省', bank_info '开户地市', end_base_pay '基础工资', seniority_pay '工龄工资', the_performance_benefits '履约补助', workdays_1 '应上班天数', workdays_2 '实际上班天数', workdays_3 '带薪天数', connect_long '总接电量', total '总产值', award_punish '产量奖/罚', average '平均产值', performance_score '绩效得分', CONCAT(draw_a_percentage,"%") '提成单价', CONCAT(base,"%") '基础系数', CONCAT(deduction,"%") '提成系数', CONCAT(synthesize,"%") '综合系数', commission_amount_huru '呼入提成', commission_amount '呼出提成', part_time_functions '绩效工资—职能、兼职管理人员绩效', travel_allowance '出差补贴', attendance_bouns '全勤', cash_award_punish '现金奖/罚', real_change_bonus '变动补贴', quantity_award '达量奖', social_security '个人社保扣款', restraining '内推奖励', shanghai_mobile_quality_penalty '上海移动质量扣罚', `make_up_last_month's_salary` '补发上月工资', cost_division '成本划分', (CASE `-- 判断第一步 信息表的 表头 否恶意离职 是合计工资为0，` when departure_flag='是' then 0 `-- 判断第二步 信息表的 表头 否恶意离职 否 合计工资再判断，那些金额加起来<0,合计工资为0,反之给合计工资` when (departure_flag='否' or departure_flag='') and ( departure_flag='否' or departure_flag='') and ( quantity_award+seniority_pay+cash_award_punish+commission_amount+ real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining )<0 then 0 when (departure_flag='否' or departure_flag='') and ( departure_flag='否' or departure_flag='') and ( quantity_award+seniority_pay+cash_award_punish+commission_amount+ real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining )>0 then round( quantity_award+seniority_pay+cash_award_punish+commission_amount+real_change_bonus #NAME? ,2) else 0 END) 税前工资合计', individual_income_tax '个人所得税', actual_salary '实发工资金额', SUBSTR( mount_guard_date, 1, 10 ) '入职时间', SUBSTR( departure_date, 1, 10 ) '离职时间', departure_flag '是否恶意离职', phone_no '联系电话' from temp_commission; `-- 呼出 M1员工 ` `-- 1、信息表` drop table if EXISTS temp_info; CREATE table temp_info SELECT a.operating_center, a.date, a.name, a.workdays_1, a.workdays_2, a.workdays_3, a.job_number, a.id_card_no, a.wage_card, a.bank_info, a.out_company_info, (CASE when a.out_company_fee!=-999999 then a.out_company_fee else 0 END) out_company_fee, a.employee_attribute, a.mount_guard_date, a.departure_date, a.departure_flag, a.phone_no, (CASE when a.seniority_pay!=-999999 then a.seniority_pay else 0 END) seniority_pay, (CASE when a.award_punish!=-999999 then a.award_punish else 0 END) award_punish, (CASE when a.cash_award_punish!=-999999 then a.cash_award_punish else 0 END) cash_award_punish, (CASE when a.attendance_bouns != -999999 then a.attendance_bouns else 0 END) attendance_bouns, a.deduction, a.base from callout_employee_info a, callout_employee_info_importbat b where a.batid=b.id and b.status=1 and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) and a.operating_center='佛山运营中心' and a.employee_attribute='M1员工'; `-- 现金奖罚` drop table if EXISTS temp_award_punish; CREATE table temp_award_punish select a.operating_center, a.wdate, a.wname, a.id_card_no, SUM( (CASE when a.fee!=-999999 then a.fee else 0 END) ) fee from callout_kfjl_info a,callout_kfjl_info_importlog b where a.batid=b.id and b.dstate=1 and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) and operating_center='佛山运营中心' GROUP BY a.id_card_no; `-- 3、社保` drop table if EXISTS temp_social; CREATE table temp_social select ss_address, ss_name, ss_id_card_no, ss_month, ss_total_personal_benefits from social where ss_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")); `-- 4、`内推奖励`` drop table if EXISTS temp_interpolate; CREATE table temp_interpolate select i_idcard_of_referrer, SUM(i_write_off_expenses_1+i_write_off_expenses_2+i_write_off_expenses_3+i_write_off_expenses_4+i_write_off_expenses_5) interpolate_total from interpolate WHERE i_data_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) GROUP BY i_idcard_of_referrer; `-- 匹配奖励及扣罚 社保` drop table if EXISTS temp_info_fee; CREATE table temp_info_fee SELECT a.operating_center, a.date, a.name, a.workdays_1, a.workdays_2, a.workdays_3, a.job_number, a.id_card_no, a.wage_card, a.bank_info, a.out_company_info, a.out_company_fee, a.employee_attribute, a.mount_guard_date, a.departure_date, a.departure_flag, a.phone_no, a.seniority_pay, a.award_punish, IFNULL(b.fee,0) cash_award_punish, a.attendance_bouns, a.deduction, a.base, IFNULL(c.ss_total_personal_benefits,0) ss_total_personal_benefits, IFNULL(d.interpolate_total,0) interpolate_total from temp_info a LEFT JOIN temp_award_punish b ON a.id_card_no=b.id_card_no left join temp_social c ON a.id_card_no=c.ss_id_card_no left join temp_interpolate d ON a.id_card_no=d.i_idcard_of_referrer; -- d.i_idcard_of_the_referrer 内推 ss_total_personal_benefits 社保 `-- 2、明细` drop table if EXISTS temp_details; CREATE table temp_details SELECT a.operating_center, a.operating_area, a.date, a.project_name, a.deal_service, a.deal_type, a.reward_unit_price, a.reward_percentage, a.mobile_number, a.job_number, a.employee_name, a.subscriber_number, a.wage_level, a.product_num_integral, sum(a.product_num_integral) total from callout_staff_workload_month a, callout_staff_workload_month_importbat b where a.batid=b.id and b.status=1 and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%")) and a.operating_center='佛山运营中心' GROUP BY a.job_number; `-- 3、信息表关联明细表` DROP TABLE IF EXISTS temp_total; `-- 存储表` CREATE TABLE temp_total select a.operating_center, a.date, a.name, a.workdays_1, a.workdays_2, a.workdays_3, a.job_number, a.id_card_no, a.wage_card, a.bank_info, a.out_company_info, a.out_company_fee, a.employee_attribute, a.mount_guard_date, a.departure_date, a.departure_flag, a.phone_no, a.seniority_pay, a.award_punish, a.cash_award_punish, a.attendance_bouns, a.deduction, a.base, a.ss_total_personal_benefits, a.interpolate_total, b.project_name, b.deal_service, b.deal_type, b.reward_unit_price, b.reward_percentage, b.mobile_number, b.subscriber_number, b.wage_level, b.product_num_integral, b.total, (CASE when (IFNULL(b.total,0)+a.award_punish)>0 then (IFNULL(b.total,0)+a.award_punish) else 0 END) product, (CASE when (IFNULL(b.total,0)+a.award_punish)>0 then (CASE when a.workdays_2>0 then (IFNULL(b.total,0)+a.award_punish) / a.workdays_2 else 0 END) else 0 END) average from temp_info_fee a LEFT JOIN temp_details b ON a.job_number=b.job_number; `-- 4、算出雅安的工资体系` drop table if EXISTS temp_system; CREATE table temp_system SELECT a.wage_level, a.computation_rule, a.production_num_min, a.production_num_max, a.cardinal_number, a.draw_a_percentage, a.base_pay, a.quantity_award, a.changes_of_subsidies, a.keep_low_wage from callout_wage_system a, callout_wage_system_importlog b where a.batid=b.id and b.dstate=1 and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),0,7),"%")) and a.wage_level='佛山运营中心-M1员工'; `-- 5、通过明细表的总产值和信息表产值奖罚，算出每个员工的月总提成` DROP TABLE IF EXISTS temp_commission; `-- 存储表` CREATE TABLE temp_commission select c.operating_center, c.date, c.name, c.workdays_1, c.workdays_2, c.workdays_3, c.job_number, c.id_card_no, c.wage_card, c.bank_info, c.out_company_info, c.out_company_fee, c.employee_attribute, c.mount_guard_date, c.departure_date, c.departure_flag, c.phone_no, c.seniority_pay, c.award_punish, c.cash_award_punish, c.attendance_bouns, c.deduction, c.base, c.project_name, c.deal_service, c.deal_type, c.reward_unit_price, c.reward_percentage, c.mobile_number, c.subscriber_number, c.product_num_integral, c.product, c.total, c.average, d.wage_level, d.computation_rule, d.production_num_min, d.production_num_max, d.cardinal_number, d.draw_a_percentage, d.base_pay, d.quantity_award, d.changes_of_subsidies, d.keep_low_wage, (CASE when (c.product-d.cardinal_number)<0 then 0 when (c.product-d.cardinal_number)>0 then (c.product-d.cardinal_number)*d.draw_a_percentage*0.01 else 0 END)*c.deduction*0.01 commission_amount, d.changes_of_subsidies*c.workdays_2 real_change_bonus, (CASE when c.workdays_1>0 then d.base_pay/c.workdays_1*(c.workdays_2+c.workdays_3) else 0 END)*0.01*c.base end_base_pay, c.seniority_pay*0 post, c.seniority_pay*0 bank_province, c.seniority_pay*0 bank_cuty, c.seniority_pay*0+100 synthesize, c.seniority_pay*0 connect_long, c.seniority_pay*0 performance_score, c.ss_total_personal_benefits social_security, c.interpolate_total restraining, c.seniority_pay*0 the_performance_benefits, c.seniority_pay*0 commission_amount_huru, c.seniority_pay*0 individual_income_tax, c.seniority_pay*0 actual_salary, c.seniority_pay*0 shanghai_mobile_quality_penalty, c.seniority_pay*0 make_up_last_month, c.seniority_pay*0 `make_up_last_month's_salary`, c.seniority_pay*0 cost_division, c.seniority_pay*0 part_time_functions, c.seniority_pay*0 travel_allowance from temp_total c,temp_system d where c.product BETWEEN d.production_num_min and d.production_num_max; -- DROP TABLE IF EXISTS temp_out_salary_1; `-- 存储表` -- CREATE TABLE temp_out_salary_1 `-- 6、工资` drop table if exists temp_out_salary_2; create table temp_out_salary_2 select operating_center '运营中心', post '岗位', name '员工姓名', job_number '员工工号', id_card_no '身份证号', wage_card '工资账号', bank_province '开户行', bank_cuty '开户地省', bank_info '开户地市', end_base_pay '基础工资', seniority_pay '工龄工资', the_performance_benefits '履约补助', workdays_1 '应上班天数', workdays_2 '实际上班天数', workdays_3 '带薪天数', connect_long '总接电量', total '总产值', award_punish '产量奖/罚', average '平均产值', performance_score '绩效得分', CONCAT(draw_a_percentage,"%") '提成单价', CONCAT(base,"%") '基础系数', CONCAT(deduction,"%") '提成系数', CONCAT(synthesize,"%") '综合系数', commission_amount_huru '呼入提成', commission_amount '呼出提成', part_time_functions '绩效工资—职能、兼职管理人员绩效', travel_allowance '出差补贴', attendance_bouns '全勤', cash_award_punish '现金奖/罚', real_change_bonus '变动补贴', quantity_award '达量奖', social_security '个人社保扣款', restraining '内推奖励', shanghai_mobile_quality_penalty '上海移动质量扣罚', `make_up_last_month's_salary` '补发上月工资', cost_division '成本划分', `-- 当M1员工有保底时，用合计工资判断保底 ； 保底=保底/（应上班天数*（实际上天数+带薪天数） keep_low_wage/(workdays_1*(workdays_2+workdays_3));;` `-- 合计<保底 给 保底 ；合计>保底 给 ；合计` (CASE when departure_flag='是' then 0 when (departure_flag='否' or departure_flag='') and ( quantity_award+seniority_pay+cash_award_punish+commission_amount+ real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining )< keep_low_wage/(workdays_1*(workdays_2+workdays_3)) then keep_low_wage/(workdays_1*(workdays_2+workdays_3)) when (departure_flag='否' or departure_flag='') and ( quantity_award+seniority_pay+cash_award_punish+commission_amount+real_change_bonus+ end_base_pay+attendance_bouns+social_security+restraining )> keep_low_wage/(workdays_1*(workdays_2+workdays_3)) then `-- 呼入合计要乘以综合系数` round( quantity_award+seniority_pay+cash_award_punish+commission_amount+ real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining ,2) else 0 END) 税前工资合计', individual_income_tax '个人所得税', actual_salary '实发工资金额', SUBSTR( mount_guard_date, 1, 10 ) '入职时间', SUBSTR( departure_date, 1, 10 ) '离职时间', departure_flag '是否恶意离职', phone_no '联系电话' from temp_commission;
最后将算出来的老员工，新员工合并在一张表 DROP TABLE IF EXISTS temp_in_all; CREATE TABLE temp_in_all select *from temp_out_salary_1 UNION ALL select *from temp_out_salary_2;
努力奔跑。。。。。
