mysql select 不适用于 IN

Question

BEGIN
DECLARE cnt1 int(11);
DECLARE cnt2 int(11);
DECLARE demandid varchar(255);
set demandid = replace(pintypeid,';',',');

SELECT 
    COUNT(*) into cnt1
FROM
    UK_Generic_Demand_Details
where status =300 and 
policyNumber =pinpno and
demandTypeId in(Select @demandid) ;

  SELECT
     COUNT(*) into cnt2
 FROM
     UK_Generic_Completed_Demand_Details
     where
      status =300 and 
policyNo =pinpno and
demandTypeId in(select @demandid);
        select cnt1+cnt2 into poutcount; 

END

有人可以建议我解决这个问题吗？将从应用程序调用存储过程，并将 pintypeid 和策略编号作为输入。policynumber 将是单个，但 pintypeid 将像一个字符串。

Answer 1

您可能正在寻找find_in_set

BEGIN
DECLARE cnt1 int(11);
DECLARE cnt2 int(11);
DECLARE demandid varchar(255);
set demandid = replace(pintypeid,';',',');

SELECT 
    COUNT(*) into cnt1
FROM
    UK_Generic_Demand_Details
where status =300 and 
policyNumber =pinpno and
find_in_set(demandTypeId, @demandid) > 0;

  SELECT
     COUNT(*) into cnt2
 FROM
     UK_Generic_Completed_Demand_Details
     where
      status =300 and 
policyNo =pinpno and
find_in_set(demandTypeId, @demandid) > 0;
        select cnt1+cnt2 into poutcount; 

END