mysql - concat 函数中 orderby 的语法错误

Question

显示像

这样的语法错误

1064-You have error in your SQL syntax.Check the manual that corresponds to your Maria Db Server version for the right syntax use near
residential_no REGEXP ('^[0-9]') DESC,CAST(residential_no AS UNSIGNED),REGEXP_SUBSTR(residential_no, '(^[a-zA-Z]+)|([a-zA-Z]$)'),CAST(REGEXP_SUBSTR(residential_no, '(^[0-9]+)|([0-9]+$)') AS UNSIGNED

CREATE DEFINER=`root`@`localhost` PROCEDURE `checking`(IN `mnth` int,IN `yr` int)
BEGIN
DECLARE cus_id text;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
SELECT GROUP_CONCAT(DISTINCT b.cust_id) FROM ttb_customer_billing b WHERE b.month_id IN (mnth) and year_id=yr   INTO cus_id;
SET @qry = CONCAT('SELECT a.*,g.assoc_name,c.ward_name,d.team_ld_name,e.volunteer_name,f.scheme_name FROM ttb_customer a left join ttb_association g on g.assoc_id=a.assoc_id
left join ttb_ward c on c.ward_id=a.ward_id left join ttb_team_lead d on d.tm_id=a.team_lead_id left join
ttb_volunteer e on e.volntr_id=a.volnt_id left join ttb_scheme f on f.scheme_id=a.scheme_id WHERE NOT  FIND_IN_SET(a.cust_id,"',cus_id,'") > 0 ORDER BY b.assoc_name asc,  residential_no REGEXP ('^[0-9]') DESC,CAST(residential_no AS UNSIGNED),REGEXP_SUBSTR(residential_no, '(^[a-zA-Z]+)|([a-zA-Z]$)'),CAST(REGEXP_SUBSTR(residential_no, '(^[0-9]+)|([0-9]+$)') AS UNSIGNED' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

Answer 1

如果您想要检查 cus_id 的索引，请尝试不使用引号序列(从 concat 返回的值已经是逗号分隔的字符串)

 WHERE NOT  FIND_IN_SET(a.cust_id,cus_id) > 0