SQL知识运用笔记2

        
1、计算工资时需要的        
        
`--体系表`        
drop table if EXISTS temp_system;  创建临时表        
CREATE table temp_system        
SELECT   复制表内容         
a.area,        
a.wiring_the_amount_min,        
a.wiring_the_amount_max,        
a.no_standard,        
a.one_standard,        
a.two_standard,        
a.three_standard,        
a.cardinal_number,        
a.basic_wage,        
a.of_the_amount_award,        
a.changes_of_subsidies,        
a.keep_low_wage        
from        
callin_wage_system a,        
callin_wage_system_importlog b        
where a.batid=b.id        
and b.dstate=1        
and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 0 MONTH),1,7),"%"))        
and a.area='贵阳运营中心普线-老员工';        
        
        
        
(CASE   sql (case when xxx then  else END) 的用法        
        when departure_flag='是' then 0
        when (departure_flag='否' or departure_flag='') and (
duty_of_age_salary+cash_award_punish+commission_amount+real_change_bonus+end_base_pay+attendance_bouns+quantity_award+social_security+restraining        
        )<0
        then 0
        when (departure_flag='否' or departure_flag='') and
        (
duty_of_age_salary+cash_award_punish+commission_amount+real_change_bonus+end_base_pay+attendance_bouns+quantity_award+social_security+restraining        
        )>0
        then round((
duty_of_age_salary+cash_award_punish+commission_amount+real_change_bonus+end_base_pay+attendance_bouns+quantity_award+social_security+restraining        
        )*synthesize*0.01,2)
    else    
    0    
END)    税前工资合计',    

 

1、数据表里数据问题，2月1日到2月28日的数据，以这个月为基础导入的数据，实际导入的是上个月的数据，用以下代码就可以实现，本月本自动取上月的数据。

and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 0 MONTH),1,7),"%")) 日期为这个月的日期-1 获取上个月的时间        

2、计算周报需要的，表里的工号，是因为Excel将数据默认为了数字。数据库是字符串形式。这时需要把“.0”去掉。

update `周报呼入工号` set yidong_id = REPLACE(yidong_id, '.0', '')    
where yidong_id like '%.0'    

3、如果要给一列数据前加上内容：

update callin_shanxi_t2    
 set gonghao_id = CONCAT('KF0',gonghao_id)    
--  where id = 1240    

 

4、如果导入的数据那列，字符串形式 6:04:51  计算是要计算为多少秒。

6:04:51     sql 将小时6:04:51计算为多少秒，分别取小时*3600=X秒+取分*60=X秒+取秒        
excel        
=（6:04:51）*24*3600        







-- 2、原始数据表1匹配工号表   有效接通量、签入系统时长、人工通话时长、示忙时长
drop table if exists temp_t1;
create table temp_t1
SELECT 
    a.yunyingzhongxin,
    a.xiangmu_area,
    a.date,
    a.yidong_id,
        SUM(IFNULL(b.tonghua_num,0)) china_num,
        SUM(b.gongzuo_times) '签入系统时长',
0 '有效人工通话量',
0 '一次解决人工服务请求量',
0'人工服务满意量',
0'参与人工服务满意度评价量',
0 '服务态度不满意量',
         SUM(IFNULL(b.tonghua_time,0)) '人工通话时长',
        SUM(IFNULL(b.shimang_time,0)) '示忙时长'
from 
(
 SELECT
    yunyingzhongxin,
    banzu,
    date,
    yidong_id,
    xiangmu_area,
    shiji_name,
    zxj_id,
    is_tc,
    ruzhi_date
    from zxjdata2.`周报呼入工号`
    where
    xiangmu_area='江西呼入'
    and date between '2019-02-14' and '2019-02-20'
) a,
(
SELECT 
date,
gonghao_id,
tonghua_num,
HOUR(gongzuo_time)*3600+MINUTE(gongzuo_time)*60+SECOND(gongzuo_time) gongzuo_times,
HOUR(tonghua_time)*3600+MINUTE(tonghua_time)*60+SECOND(tonghua_time) tonghua_time,
HOUR(shimang_time)*3600+MINUTE(shimang_time)*60+SECOND(shimang_time) shimang_time
from callin_jiangxi_t1
where `date` between '2019-02-14' and '2019-02-20'
) b
where a.yidong_id=b.gonghao_id
and a.date=b.`date`
GROUP BY a.xiangmu_area;

 

首先表多，可以一步一步计算并存储为一张零时表，这样就少一些代码        
        
合计那步        
个人所得税公式        
=ROUND(MAX((G4-H4-I4-5000)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,210,1410,2660,4410,7160,15160},0),2)，这个公式我看不懂，我怎么引用到我的工资表里        
        
=ROUND(MAX((AP314-5000)*{0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;210;1410;2660;4410;7160;15160},),2)