/**
*根据省份id和时间查询不同班次累计域名和url次数
**/
DROP PROCEDURE IF EXISTS proc_get_province_count_info;
CREATE PROCEDURE proc_get_province_count_info (
IN province_id INT,
IN start_time varchar(50),
IN end_time varchar(50),
OUT domain_count INT,
OUT url_count INT,
OUT last_domain_count INT,
OUT last_url_count INT
)
BEGIN
-- 查询所有省份id
DECLARE done INT DEFAULT 0;
DECLARE cur_province_ids CURSOR FOR SELECT distinct subject_id FROM shop_subject_user_relation;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 本班次累计域名
OPEN cur_province_ids;
province_ids: LOOP
FETCH cur_province_ids INTO province_id;
IF done = 1 THEN -- 循环结束
LEAVE province_ids;
ELSE
SELECT
count(*) INTO domain_count
FROM
shop_work_order
where url <>'' group by url;
-- 本班次累计URL
SELECT
count(*) INTO url_count
FROM
shop_work_order sp
where url <>'' group by url;
-- 上班次累计域名
SELECT
count(*) INTO last_domain_count
FROM
shop_work_order
where url <>'' group by url;
-- 上班次累计URL
SELECT
count(*) INTO last_url_count
FROM
shop_work_order
where url <>'' group by url;
END IF;
END LOOP province_ids;
CLOSE cur_province_ids;
END
-- 调用
call proc_get_province_count_info('1','2','2',@d1,@d2,@d3,@d4);
SELECT @d1,@d2,@d3,@d4;