sql存储过程，查询累加数据

分手后的思念是犯贱 2021-07-26 18:46 417阅读 0赞 
  1. CREATE DEFINER=`anjiplus`@`%` PROCEDURE `get_v_wms_report_working_hour`(
  2.     in_whse_id VARCHAR(100),
  3.     in_operation_date VARCHAR(100),
  4.     in_type VARCHAR(100)
  5.     )
  6. BEGIN
  7. SET @csum:=0;
  8. SET @tsum:=0;
  9. SELECT 
  10.   a.whse_id,
  11.   a.operation_date,
  12.   CONCAT(hourtime, ':', mintime) AS HOUR,
  13.   ROUND(SUBSTRING_INDEX(unit_vol, '/', 1),2) AS v,
  14.   (@csum:= @csum + ROUND(IFNULL(SUBSTRING_INDEX(unit_vol, '/', 1),0),2)) AS sum_v,
  15.   theory_v,
  16.   (@tsum:= @tsum +ROUND(IFNULL(theory_v,0),2)) AS sum_theory
  17. FROM
  18.   v_wms_report_working_hour a
  19.   LEFT JOIN (
  20. SELECT 
  21. whse_id,
  22. operation_date,
  23. ROUND(total_vol/(total_hour*2),3) AS theory_v 
  24. FROM v_wms_report_working_total 
  25. WHERE TYPE=in_type 
  26. AND  whse_id = in_whse_id 
  27.   AND operation_date = in_operation_date
  28.   ) b ON a.whse_id=b.whse_id AND a.operation_date=b.operation_date
  29. WHERE a.whse_id = in_whse_id 
  30.   AND a.operation_date = in_operation_date
  31.   AND a.TYPE = in_type;
  32.     END

发表评论

表情：
评论列表 （有 0 条评论，417人围观）

还没有评论，来说两句吧...

相关阅读