改的差不多了,还有个实在不知道怎么改了 DELIMITER $$USE `CloudOrganization`$$DROP FUNCTION IF EXISTS `fn_staff_trimjobpath`$$CREATE DEFINER=`root`@`%` FUNCTION `fn_staff_trimjobpath`(oripath VARCHAR(500), appID BIGINT, jobID BIGINT) RETURNS VARCHAR(500) CHARSET utf8 BEGIN DECLARE pathvalue VARCHAR(500); SELECT CONCAT(CAST(GROUP_CONCAT(b.ID ORDER BY FIND_IN_SET(CAST(b.ID AS CHAR(50)), oripath) ASC) AS CHAR(10000)), ',') INTO pathvalue FROM Job b WHERE INSTR(CONCAT(',', oripath), CONCAT(',', b.id, ',')) > 0 AND b.id NOT IN (SELECT JobID FROM JobApplicationDetail WHERE id = appID AND NewOrgID > 0 UNION SELECT jobID); RETURN pathvalue; END$$DELIMITER ;主要是CONCAT(CAST(GROUP_CONCAT(b.ID ORDER BY FIND_IN_SET(CAST(b.ID AS CHAR(50)), oripath) ASC) AS CHAR(10000)), ',')这里,看得我头大~
DELIMITER $$USE `CloudOrganization`$$DROP FUNCTION IF EXISTS `fn_staff_trimjobpath`$$CREATE DEFINER=`root`@`%` FUNCTION `fn_staff_trimjobpath`(oripath VARCHAR(500), appID BIGINT, jobID BIGINT) RETURNS VARCHAR(500) CHARSET utf8
BEGIN
DECLARE pathvalue VARCHAR(500);
SELECT CONCAT(CAST(GROUP_CONCAT(b.ID ORDER BY FIND_IN_SET(CAST(b.ID AS CHAR(50)), oripath) ASC) AS CHAR(10000)), ',') INTO pathvalue
FROM Job b
WHERE INSTR(CONCAT(',', oripath), CONCAT(',', b.id, ',')) > 0
AND b.id NOT IN (SELECT JobID FROM JobApplicationDetail WHERE id = appID AND NewOrgID > 0 UNION SELECT jobID);
RETURN pathvalue;
END$$DELIMITER ;主要是CONCAT(CAST(GROUP_CONCAT(b.ID ORDER BY FIND_IN_SET(CAST(b.ID AS CHAR(50)), oripath) ASC) AS CHAR(10000)), ',')这里,看得我头大~