Mysql 游标(加强篇)

2017-02-09  本文已影响0人  码谜妈咪

今天和沈老师又学到了一招,(*^__^*) 嘻嘻……——游标的更高级的使用

相关字段有glstr , username , rename,pos以图例讲解几者关系:

username:该会员的用户名

prename: 该会员的上级接点会员的用户名,如Bl,Br的prename都是A

pos: 该会员的位置,1=左,2=右

glstr: 树状图所经过的节点,即该会员的所有上级接点,如C1r会员的glstr为 :A,Bl

问题:求取dg_users表中字段username的所有会员的左右两边的人数(左右子节点数);

解析:

1. 查询出所有的username,遍历结果集,

select  `username` from `dg_users` ;

2. 比如username为*A*的左右两个直接子节点Y_left,Y_right(注:该会员可能不存在下级,也可能只有一个左子节点,或右子节点)

select `username`  ,`pos` from `dg_users` where `prename`='*A*';

(注:这里要判断pos,为1时为左,为2时为右);

3.求Y_left,Y_right两个子节点的子节点数count_L , count_R

select count(id) from `dg_users` where `glstr` likeCONCAT("%,",*A*,",%") orCONCAT("%,",*A*) orCONCAT(*A*,",%");

4. 加法计算,count_L+1,count_R+1就是username为**的左右子节点数了

update `dg_users` set `y_left`=count_L+1,`y_right`=count_R+1 where `username` = '*A*';

5.现在重点来啦!!!我们用前面学到的游标对上面的sql语句进行整理,(要用到两个游标哦,好期待ing~~)

(1)、2,3步骤写为一个游标函数:


```

BEGIN

declare_sibegin_pos VARCHAR(50);

DECLARE_sibegin_username VARCHAR(50);

DECLAREdone INT DEFAULT FALSE;

DECLAREcurCURSOR FORSELECT pos,username FROM jingluovip.dg_users WHERE glstr LIKE CONCAT(sibegin_username,"%");

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO _sibegin_pos,_sibegin_username;

IF done THEN

LEAVE read_loop;

END IF;

# 按用户名称查询数据

IF _sibegin_pos = 1 THEN

# left

SELECT count(glstr) INTO out_left FROM jingluovip.dg_users WHERE glstr LIKE CONCAT("%,",_sibegin_username,",%") OR glstr LIKE CONCAT(_sibegin_username,",%") OR glstr LIKE CONCAT("%,",_sibegin_username);

ELSE

SELECT count(glstr) INTO out_right FROM jingluovip.dg_users WHERE glstr LIKE CONCAT("%,",_sibegin_username,",%") OR glstr LIKE CONCAT(_sibegin_username,",%") OR glstr LIKE CONCAT("%,",_sibegin_username);

END IF;

END LOOP;

CLOSE cur;

END

```

该函数命名为sp_user_jiesuan2

(2)、1,4步骤写为一个游标函数:

BEGIN

```

DECLAREsibegin_username VARCHAR(50);

DECLAREsibegin_left INT;

DECLAREsibegin_right INT;

DECLARE done INT DEFAULT FALSE;

-- DECLARE cur CURSOR FOR SELECT `username` FROM jingluovip.dg_users_copy WHERE username = '13507378136';

DECLARE cur CURSOR FOR SELECT `username` FROM jingluovip.dg_users;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO sibegin_username;

IF done THEN

LEAVE read_loop;

END IF;

# 按用户名称查询数据

call sp_user_jiesuan2(sibegin_username,@l,@r);

UPDATE jingluovip.dg_users SET jingluovip.dg_users.y_left = @l + 1, jingluovip.dg_users.y_right = @r + 1 WHERE username = sibegin_username;

END LOOP;

CLOSE cur;

END

```

6.结束语:

啦啦啦,怎么样,是不是感觉很高大上(⊙o⊙)? 对沈老师的膜拜又上升了一个高度 O(∩_∩)O哈哈~

上一篇下一篇

猜你喜欢

热点阅读