每日一得--mysql函数与存储过程笔记

mysql精确到四位有效小数金额去掉多余0的函数：

DROP FUNCTION IF EXISTS amountFormat_fun;
DELIMITER $
CREATE FUNCTION `amountFormat_fun`(`in_amount` DECIMAL(20, 4)) RETURNS varchar(20) CHARSET utf8
    NO SQL
BEGIN


  DECLARE AMOUNT_RETURN VARCHAR(20);
  DECLARE AMOUNT_RIGHT VARCHAR(20);
  DECLARE AMOUNT_LEFT VARCHAR(20);
  DECLARE AMOUNT_RIGHT_FOUR VARCHAR(20);


  SET AMOUNT_RETURN = CAST(in_amount AS CHAR);
  SET AMOUNT_LEFT = SUBSTRING_INDEX(AMOUNT_RETURN,'.',1);
  SET AMOUNT_RIGHT_FOUR = RIGHT(AMOUNT_RETURN,4);




  SET AMOUNT_RIGHT = RIGHT(AMOUNT_RIGHT_FOUR,1);
IF (AMOUNT_RIGHT != '0') THEN
SET AMOUNT_RIGHT_FOUR  = LEFT(AMOUNT_RIGHT_FOUR,4);
RETURN CONCAT(AMOUNT_LEFT,'.',AMOUNT_RIGHT_FOUR);
END IF;




  SET AMOUNT_RIGHT = RIGHT(AMOUNT_RIGHT_FOUR,2);
IF (AMOUNT_RIGHT != '00') THEN
SET AMOUNT_RIGHT_FOUR  = LEFT(AMOUNT_RIGHT_FOUR,3);
RETURN CONCAT(AMOUNT_LEFT,'.',AMOUNT_RIGHT_FOUR);
END IF;


  SET AMOUNT_RIGHT = RIGHT(AMOUNT_RIGHT_FOUR,3);
IF (AMOUNT_RIGHT != '000') THEN
SET AMOUNT_RIGHT_FOUR  = LEFT(AMOUNT_RIGHT_FOUR,2);
RETURN CONCAT(AMOUNT_LEFT,'.',AMOUNT_RIGHT_FOUR);
END IF;


  SET AMOUNT_RIGHT = RIGHT(AMOUNT_RIGHT_FOUR,4);
IF (AMOUNT_RIGHT != '0000') THEN
SET AMOUNT_RIGHT_FOUR  = LEFT(AMOUNT_RIGHT_FOUR,1);
RETURN CONCAT(AMOUNT_LEFT,'.',AMOUNT_RIGHT_FOUR);
END IF;


RETURN AMOUNT_LEFT;
END$

DELIMITER ;

mysql预算处理存储过程：


DROP PROCEDURE IF EXISTS n_userBudgets;
DELIMITER $
CREATE PROCEDURE `n_userBudgets`(IN `userId` int,IN `companyId` int,IN `type` int,IN `time` varchar(10),IN `timeType` int)
BEGIN

DECLARE queryMonth VARCHAR(10);
DECLARE var INT;
  DECLARE varStr VARCHAR(10);


SET @queryUser='';
SET @queryUser_b='';
  SET @queryUser_a='';
  SET @queryUser_tn='';
IF  userId IS NOT NULL THEN 
SET @queryUser =CONCAT(' and r.userId = ',userId);
SET @queryUser_b =CONCAT(' and b.userId = ',userId);
SET @queryUser_a =CONCAT(' and a.userId = ',userId);
SET @queryUser_tn =CONCAT(' and tn.userId = ',userId);
END IF;
IF  companyId IS NOT NULL THEN 
SET @queryUser =CONCAT(@queryUser,' and r.companyId = ',companyId);
SET @queryUser_b =CONCAT(@queryUser_b,' and b.companyId = ',companyId);
SET @queryUser_a =CONCAT(@queryUser_a,' and a.companyId = ',companyId);
SET @queryUser_tn =CONCAT(@queryUser_tn,' and tn.companyId = ',companyId);


END IF;


  
IF  timeType =0 THEN 

SET @sql_text=CONCAT('SELECT r.userId as staffId,u.nickName as staffName,u.avatar as staffLogo,r.companyId,IFNULL(r.happenAmount,0) as happenAmount,IFNULL(r.budgetAmount,0) as budgetAmount,IFNULL(r.completionRate,0) as completionRate from (
SELECT result.userId,result.companyId,SUM(result.noteMonthAmount)  as happenAmount,SUM(result.budget) as budgetAmount,convert(SUM(result.noteMonthAmount)/SUM(result.budget),decimal(10,4)) as completionRate from (
SELECT bt.userId,bt.companyId,(CASE WHEN bt.budgetAmount is NULL THEN 0 ELSE IFNULL(SUM(n.amount),0) END) as noteMonthAmount,bt.budgetAmount as budget from  (
SELECT c.userId,c.companyId,(CASE  WHEN  b.amount is NULL THEN (select bb.amount from t_rc_budget bb where STR_TO_DATE(bb.month,\'%Y%m\') < STR_TO_DATE(',time,',\'%Y%m\') and bb.type = ',type,' and bb.userId = c.userId and bb.companyId = c.companyId ORDER BY bb.month DESC LIMIT 1)
ELSE b.amount
END) as budgetAmount from t_bd_companyuser c LEFT JOIN t_rc_budget b ON b.month=',time,' and b.userId = c.userId and b.companyId = c.companyId and c.companyId= ',companyId,' and b.type = ',type,'
) as bt  LEFT JOIN t_rc_note n on bt.userId = n.userId and bt.companyId = n.companyId  and   DATE_FORMAT(n.recordTime,\'%Y%m\')=',time,'  and n.pending = 0 and n.status = 1 and n.reimburse=0  and n.type = ',type,' 
GROUP BY bt.userId,bt.companyId,DATE_FORMAT(n.recordTime,\'%Y%m\')
) as result  GROUP BY result.userId,result.companyId
) as r LEFT JOIN t_bd_user u ON u.userId = r.userId where 1=1  ',@queryUser,' ORDER BY  r.completionRate DESC ,r.happenAmount DESC, r.budgetAmount DESC');


PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
DEALLOCATE PREPARE stmt;


  
ELSEIF  timeType =1 THEN


    
    set @A = date_add(STR_TO_DATE(time,'%Y%m%d'), interval -1 day);  
    
    set @startDate = subdate( @A,date_format(@A,'%w')-1);  
    
    set @endDate = date_add(@startDate, interval 6 day);  


set @dmonth = DATE_FORMAT(@startDate,'%Y%m');


IF DATE_FORMAT(@startDate,'%Y%m')=DATE_FORMAT(@endDate,'%Y%m') THEN
SET @startMonthDaysf = DAYOFMONTH(last_day(STR_TO_DATE(@startDate,'%Y-%m-%d')));
SET @sql_text=CONCAT('SELECT r.userId as staffId,u.nickName as staffName,u.avatar as staffLogo,r.companyId,IFNULL(r.happenAmount,0) as happenAmount,IFNULL(r.budgetAmount,0) as budgetAmount,IFNULL(r.completionRate,0) as completionRate from (
SELECT result.userId,result.companyId,SUM(result.noteMonthAmount)  as happenAmount,SUM(result.budget) as budgetAmount,convert(SUM(result.noteMonthAmount)/SUM(result.budget),decimal(10,4)) as completionRate from (
SELECT bt.userId,bt.companyId,(CASE WHEN bt.budgetAmount is NULL THEN 0 ELSE IFNULL(SUM(n.amount),0) END) as noteMonthAmount,bt.budgetAmount*7/',@startMonthDaysf,' as budget from  (
SELECT c.userId,c.companyId,(CASE  WHEN  b.amount is NULL THEN (select bb.amount from t_rc_budget bb where STR_TO_DATE(bb.month,\'%Y%m\') < STR_TO_DATE(',@dmonth,',\'%Y%m\') and bb.type = ',type,' and bb.userId = c.userId and bb.companyId = c.companyId ORDER BY bb.month DESC LIMIT 1)
ELSE b.amount
END) as budgetAmount from t_bd_companyuser c LEFT JOIN t_rc_budget b ON b.month=',@dmonth,' and b.userId = c.userId and b.companyId = c.companyId  and b.type = ',type,'
) as bt  LEFT JOIN t_rc_note n on bt.userId = n.userId and bt.companyId = n.companyId  and    DATE_FORMAT(n.recordTime,\'%Y%m%d\') >= STR_TO_DATE(\'',@startDate,'\',\'%Y-%m-%d\') and  DATE_FORMAT(n.recordTime,\'%Y%m%d\') <= STR_TO_DATE(\'',@endDate,'\',\'%Y-%m-%d\')  and n.pending = 0 and n.status = 1 and n.reimburse=0  and n.type = ',type,'
GROUP BY bt.userId,bt.companyId,DATE_FORMAT(n.recordTime,\'%Y%m\')
) as result  GROUP BY result.userId,result.companyId
) as r LEFT JOIN t_bd_user u ON u.userId = r.userId where 1=1 ',@queryUser,' ORDER BY  r.completionRate DESC ,r.happenAmount DESC, r.budgetAmount DESC');
PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
DEALLOCATE PREPARE stmt;


ELSE 
    
set @startMonthDate = date_add(STR_TO_DATE(@endDate,'%Y-%m-%d'), interval - day(STR_TO_DATE(@endDate,'%Y-%m-%d')) + 1 day);  
   
set @endMonthDate = last_day(STR_TO_DATE(@startDate,'%Y-%m-%d'));  
   
set @startIntervalDateS = to_days(@endMonthDate) - to_days(@startDate)+1;
   
        set @endIntervalDateS = to_days(@endDate) - to_days(@startMonthDate)+1;
   
SET @startMonthDays = DAYOFMONTH(last_day(STR_TO_DATE(@startDate,'%Y-%m-%d')));
   
SET @endMonthDays = DAYOFMONTH(last_day(STR_TO_DATE(@endDate,'%Y-%m-%d')));


set @smonth = DATE_FORMAT(@startDate,'%Y%m');
set @emonth = DATE_FORMAT(@endDate,'%Y%m');


SET @month_budget=CONCAT(' SELECT r.userId,r.companyId,r.month,(CASE WHEN r.month = ',@smonth,' THEN (r.budgetAmount*',@startIntervalDateS,')/',@startMonthDays,' ELSE (r.budgetAmount*',@endIntervalDateS,')/',@endMonthDays,' END) as budgetAmount from (
SELECT t.userId,t.companyId,t.month,
(CASE  WHEN  b.amount is NULL THEN (select bb.amount from t_rc_budget bb where STR_TO_DATE(bb.month,\'%Y%m\') < STR_TO_DATE(t.month,\'%Y%m\') and bb.type = ',type,' and bb.userId = t.userId and bb.companyId = t.companyId ORDER BY bb.month DESC LIMIT 1)
ELSE b.amount
END) as budgetAmount
from  (
select c.userId,c.companyId,tt.month from t_bd_companyuser c,(
SELECT ',@smonth,' as month  
union 
SELECT ',@emonth,' as month 
) as tt
) as t
LEFT   JOIN  t_rc_budget b ON t.month = b.month and b.userId = t.userId and b.companyId = t.companyId and b.type = ',type,' ) as r');


SET @sql_text=CONCAT(' SELECT r.userId as staffId,u.nickName as staffName,u.avatar as staffLogo,r.companyId,IFNULL(r.happenAmount,0) as happenAmount,IFNULL(r.budgetAmount,0) as budgetAmount,completionRate from (
SELECT bt.userId,bt.companyId,IFNULL(SUM(n.amount),0) as happenAmount,IFNULL(bt.allBudgetAmount,0) as budgetAmount,(CASE WHEN bt.allBudgetAmount IS NULL THEN 0 WHEN bt.allBudgetAmount =0 THEN 0 ELSE convert(IFNULL(SUM(n.amount),0)/bt.allBudgetAmount,decimal(10,4)) END) as completionRate from  (
SELECT o.userId,o.companyId,o.month,o.budgetAmount,s.allBudgetAmount from (
',@month_budget,'
) as o
LEFT JOIN
(
SELECT a.userId,a.companyId,IFNULL(SUM(a.budgetAmount),0) as allBudgetAmount from (
',@month_budget,'
) as a  GROUP BY a.userId,a.companyId
) as s on s.userId = o.userId and s.companyId = o.companyId
) as bt  LEFT JOIN t_rc_note n ON  bt.userId = n.userId and bt.budgetAmount IS NOT NULL and bt.budgetAmount <> 0 and DATE_FORMAT(n.recordTime,\'%Y%m\')=bt.month and bt.companyId = n.companyId  and    DATE_FORMAT(n.recordTime,\'%Y%m%d\') >= STR_TO_DATE(\'',@startDate,'\',\'%Y-%m-%d\') and  DATE_FORMAT(n.recordTime,\'%Y%m%d\') <= STR_TO_DATE(\'',@endDate,'\',\'%Y-%m-%d\')  and n.pending = 0 and n.status = 1 and n.reimburse=0  and n.type = ',type,' 
GROUP BY bt.userId,bt.companyId
) as r LEFT JOIN t_bd_user u ON u.userId = r.userId where 1=1 ',@queryUser,'  ORDER BY  r.completionRate DESC ,r.happenAmount DESC, r.budgetAmount DESC');
PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END IF;
 
  
ELSEIF  timeType =2 THEN 
SET var = 1;
SET @sql_month='';
WHILE var<13 DO
SET varStr = '';
IF var <10 THEN SET varStr = CONCAT('0',var);
ELSE SET varStr = var;
END IF;
SET queryMonth = CONCAT(time,varStr);
IF var =1 THEN SET @sql_month = CONCAT(@sql_month,' SELECT ',queryMonth,' as month ');
ELSE SET @sql_month = CONCAT(@sql_month,' union SELECT ',queryMonth,' as month ');
END IF;
SET var = var +1;
END WHILE;


SET @month_budget =CONCAT('SELECT t.userId,t.companyId,t.month,
(CASE  WHEN  b.amount is NULL THEN (select bb.amount from t_rc_budget bb where STR_TO_DATE(bb.month,\'%Y%m\') < STR_TO_DATE(t.month,\'%Y%m\') and bb.type = ',type,' and bb.userId = t.userId and bb.companyId = t.companyId ORDER BY bb.month DESC LIMIT 1)
ELSE b.amount
END) as budgetAmount
from  (
select c.userId,c.companyId,tt.month from t_bd_companyuser c,( ',@sql_month,' ) as tt ) as t
LEFT   JOIN  t_rc_budget b ON t.month = b.month and b.userId = t.userId  and b.companyId = t.companyId  and b.type = ',type);




SET @sql_text= CONCAT('SELECT r.userId as staffId,u.nickName as staffName,u.avatar as staffLogo,r.companyId,IFNULL(r.happenAmount,0) as happenAmount,IFNULL(r.budgetAmount,0) as budgetAmount,completionRate from (
SELECT bt.userId,bt.companyId,IFNULL(SUM(n.amount),0) as happenAmount,IFNULL(bt.allBudgetAmount,0) as budgetAmount,(CASE WHEN bt.allBudgetAmount IS NULL THEN 0 WHEN bt.allBudgetAmount =0 THEN 0 ELSE convert(IFNULL(SUM(n.amount),0)/bt.allBudgetAmount,decimal(10,4)) END) as completionRate from  (
SELECT tn.userId,tn.companyId,tn.month,IFNULL(tn.budgetAmount,0) as budgetAmount,ta.allBudgetAmount from (',@month_budget,' ) as tn LEFT JOIN (SELECT a.userId,a.companyId,IFNULL(SUM(a.budgetAmount),0) as allBudgetAmount from ( ',@month_budget,') as a where 1=1 ',@queryUser_a,'  GROUP BY a.userId,a.companyId) as ta ON ta.userId = tn.userId and ta.companyId = tn.companyId where 1=1 ',@queryUser_tn,'
) as bt  LEFT JOIN t_rc_note n ON  bt.userId = n.userId and bt.companyId = n.companyId  and bt.budgetAmount IS NOT NULL and bt.budgetAmount <> 0 and   bt.month = DATE_FORMAT(n.recordTime,\'%Y%m\')  and n.pending = 0 and n.reimburse=0 and n.status = 1 and n.type = ',type,' 
GROUP BY bt.userId,bt.companyId
) as r LEFT JOIN t_bd_user u ON u.userId = r.userId where 1=1 ',@queryUser,' ORDER BY  r.completionRate DESC ,r.happenAmount DESC, r.budgetAmount DESC');


PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END$
DELIMITER ;