MySQL 创建\调用存储过程

来源：互联网发布：恒大淘宝编辑：程序博客网时间：2024/06/05 04:06

/*********************************创建存储过程*****************************************/

DELIMITER $$USE `kxtx-jrbi`$$DROP PROCEDURE IF EXISTS `lianxu`$$CREATE DEFINER=`jrbiuser`@`%` PROCEDURE `lianxu`(IN n INT,IN start_date DATE,IN end_date DATE)BEGINSELECT COUNT(DISTINCT uid)  FROM(SELECT uid, MAX(days) lianxu_days, MIN(login_day) start_date,MAX(login_day) end_date   FROM (SELECT uid,               @cont_day :=               (CASE                 WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN                  (@cont_day + 1)                 WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)<1) THEN                  (@cont_day + 0)                 ELSE                  1               END) AS days,               (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,               @last_uid := uid,               @last_dt := login_dt login_day          FROM (          SELECT uid,login_dt FROM          (          SELECT out_company AS  uid ,  trading_date AS login_dt FROM `t_platform_trade_base` WHERE trading_date BETWEEN start_date AND end_dateAND waybill_type='自开单'  UNION SELECT uk_company, trading_date FROM `t_platform_trade_base` WHERE trading_date BETWEEN start_date AND end_dateAND waybill_type='承接单' ) a1       ORDER BY  uid,login_dt           ) AS t,               (SELECT @last_uid := '',                       @last_dt  := '',                       @cont_ix  := 0,                       @cont_day := 0) AS t1) AS t2 GROUP BY uid, cont_ix )a  WHERE lianxu_days>=n;    END$$DELIMITER ;

/************************调用存储过程**********************************/

call(5,'2016-02-01','2017-02-01'); -- 返回2016-02-01到2017-02-01 期间活跃次数至少5次的人数

0 0