自己随便写了一个统计用户权限数量插入对应表的案例
CREATE TABLE IF NOT EXISTS u_user_role_count (
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT "表ID",
`uid` BIGINT NOT NULL COMMENT "用户ID",
`uname` VARCHAR(16) DEFAULT NULL COMMENT "用户名",
`rname` VARCHAR(16) DEFAULT NULL COMMENT "权限名",
`count` BIGINT NOT NULL COMMENT "总计",
PRIMARY KEY (id)
)ENGINE=INNODB,DEFAULT CHARSET=utf8;
###########################统计用户权限数量##############################
#存储过程
DROP PROCEDURE GetAllProducts;
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
DECLARE UID BIGINT;
DECLARE UNAME VARCHAR(16);
DECLARE COUNTN INT;
DECLARE done INT DEFAULT 0;
DECLARE total INT;
DECLARE set_user CURSOR FOR SELECT `id` FROM u_user; ##建立游标
SELECT COUNT(id) INTO total FROM u_role;
OPEN set_user;
al: LOOP
FETCH set_user INTO UID; ##遍历并使用游标,取出里面每个ID
SELECT COUNT(u_user.id) INTO COUNTN
FROM u_user,u_user_role,u_role
WHERE u_user.id=UID AND u_user_role.uid=UID AND u_user_role.rid=u_role.id; ##计数权限数量
SELECT `nickname` INTO UNAME FROM u_user WHERE id=UID; ##查出用户名
INSERT INTO u_user_role_count (uid,uname,count) VALUES (UID,UNAME,COUNTN); ##插入对应表格
set done = done+1;
IF done = total THEN
LEAVE al;
END IF;
END LOOP al;
CLOSE set_user;
END //
DELIMITER ;
################################权限计数####################################
DROP PROCEDURE EachUser;
DELIMITER //
CREATE PROCEDURE EachUser(
UID BIGINT
)
BEGIN
SELECT COUNT(*)
FROM u_user,u_user_role,u_role
WHERE u_user.id=UID AND u_user_role.uid=UID AND u_user_role.rid=u_role.id;
END//
DELIMITER ;
################################测试#####################################
call GetAllProducts();
call EachUser(1);
CREATE TEMPORARY TABLE tmp SELECT * FROM CALL EachUser(1);
下面是统计某角色所有用户的数量
CREATE TABLE IF NOT EXISTS u_role_user_count(
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`rid` BIGINT NOT NULL COMMENT '角色ID',
`rname` VARCHAR(16) NOT NULL COMMENT '角色名称',
`count` INT NOT NULL COMMENT '计数',
PRIMARY KEY (id)
)ENGINE=INNODB,DEFAULT CHARSET=utf8;
DROP PROCEDURE CountRoleUsers;
DELIMITER //
CREATE PROCEDURE CountRoleUsers()
BEGIN
DECLARE RID BIGINT;
DECLARE RNAME VARCHAR(16);
DECLARE COUNTN INT;
DECLARE done INT DEFAULT 0;
DECLARE total INT;
DECLARE set_role CURSOR FOR SELECT `id`,`name` FROM u_role;
SELECT COUNT(id) INTO total FROM u_role;
OPEN set_role;
lb: LOOP
FETCH set_role INTO RID,RNAME;
SELECT COUNT(u_role.id) INTO COUNTN
FROM u_role,u_user_role,u_user
WHERE u_user.id=u_user_role.uid AND u_user_role.rid=RID AND u_role.id=RID;
INSERT INTO u_role_user_count (rid,rname,count) VALUES (RID,RNAME,COUNTN);
SET done=done+1;
IF done = total THEN
LEAVE lb;
END IF;
END LOOP lb;
CLOSE set_role;
END //
DELIMITER ;
CALL CountRoleUsers();