base_users表包含User_ID和parentid两个字段.要求传入User_ID,查询出查询出用户,以及其下级用户(多个下级,下级还有下级).
我使用的方法
函数:
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(pUID AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(User_ID) INTO oTempChild FROM base_users WHERE FIND_IN_SET(ParentId,oTempChild) > 0;
END WHILE;
if(length(oTemp)>0)
then
begin
return substring(oTemp,2);
end;
else
return oTemp;
end if;
END
存储过程调用:
BEGIN
declare pUser_Type int;
declare pids text;
select GROUP_CONCAT(User_ID) into pids from base_users where FIND_IN_SET(user_id, getChildUser(pUser_ID));
SELECT User_ID,ParentId,User_Name,User_Type FROM base_users where deletemark=0 and FIND_IN_SET(User_ID,pids) ;
存储过程中还有查询需要用到pids.
这样做 查询好慢总共2000+数据,结果24行,要10+秒.
求大神优化指点.
(用FIND_IN_SET(ParentId, @pids) > 0, @pids := CONCAT(@pids, ',', User_ID), 0) AS ischild 方法会有的下级查询不出来)
