DELIMITER ;; /*重新定义结束符,存储过程结束要定义回来,成对使用,主要是END;;在使用*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_calculate_user_active_strategy`( /* CREATE DEFINER=`root`@`localhost` 为定义权限,此为默认值 */
in user_id varchar(36), /*in是传入的参数,user_id是参数名, varchar(36)是类型.注意参数名尽量不与数据库字段重名,容易造成数据库判断时混淆,或者使用u.id = id也行*/
in platform tinyint,
out strategy_id varchar(36) /*输出参数,需要select 才能获取到这个值*/
) /*BEGIN和END是查询语句的开始和结束*/
BEGIN
-- 用户直属部门的full_department_id
declare user_full_department_id varchar(1000); /*声明变量,与var同一功能*/
/*
变量赋值的方法:
1.set var_name=expression
2.select field_name into var_name field_name可以是重命名的字段
*/
-- 获取当前用户所在部门的全路径
select d.full_department_id into user_full_department_id from user u
inner join department d on u.department_id = d.id
where u.id = user_id;
/*
联合查询,从结果集中查询
select s_id into strategy_id from (() union ()) rs;
rs为结果集的名称
union是合集,并去重
s.id as s_id, s.weight as s_weight 将每一条语句查询出来的结果,合并在一起组成新的合集
然后select s_id into strategy_id from 从合集中查出s_id
*/
select s_id into strategy_id from (
-- 在strategy_user表中找到权重最高的 strategy_id
(select s.id as s_id, s.weight as s_weight from strategy_user su
inner join strategy s on su.strategy_id = s.id
where su.user_id = user_id and s.platform = platform
order by s.weight desc limit 1)
union
-- 在strategy_department表中找到权重最高的 strategy_id
(select s.id as s_id, s.weight as s_weight from strategy_department sd
inner join strategy s on sd.strategy_id = s.id
inner join department d on sd.department_id = d.id
where d.full_department_id = substring(user_full_department_id, 1, char_length(d.full_department_id)) and s.platform = platform
order by s.weight desc limit 1)
/*
union
-- 在strategy_group表中找到权重最高的 strategy_id
(select s.id as s_id, s.weight as s_weight from strategy_group sg
inner join strategy s on sg.strategy_id = s.id
inner join `group` g on sg.group_id = g.id
where s.platform = platform and g.id in (select distinct group_id from group_user where user_id = user_id)
order by s.weight desc limit 1)
*/
) rs order by rs.s_weight desc limit 1;
END ;;
DELIMITER ;