我有一个带有字符串参数的存储过程。此参数以'759,760,761'分隔。在where子句中,我想这样使用它。

...AND REL.CountyId IN (FIND_IN_SET(REL.CountyId, '759,760,761') > 0)


但是没有返回的数据,但是有759个县ID的行。如果删除此子句,数据就来了。

这是我所有的sql代码:

    SELECT
    REL.RealEstateListingId,
    (SELECT
            ImageUrl
        FROM
            realestateimage
        WHERE
            IsMainImage = 1 AND Status = 1
                AND RealEstateListingId = REL.RealEstateListingId) AS ImageUrl,

    REL.ListingNumber,
    REL.Caption,
    REL.M2,
    REL.RoomCount,
    /*CONCAT((SELECT Name FROM City WHERE CityId IN (SELECT CityId FROM County WHERE CountyId = REL.CountyId)),'/', (SELECT Name FROM County WHERE CountyId = REL.CountyId)) AS Region,*/
    REL.Price,
    REL.Creation,
    REL.CountyId
FROM
    realestatelisting AS REL
        INNER JOIN
    User AS U ON U.UserId = REL.CreatedBy
        /*INNER JOIN realestatefilterrelation AS RFR ON RFR.RealEstateId = REL.RealEstateListingId*/
WHERE  REL.Price  BETWEEN 0 AND 1000000
   AND CAST(REL.M2 AS SIGNED) BETWEEN 0 AND 1000
   /*AND REL.CountyId IN (FIND_IN_SET(REL.CountyId, '759') > 0)

  /*AND RFR.FilterContentId IN(FIND_IN_SET(RFR.FilterContentId, filterIds) > 0)*/
ORDER BY REL.Creation;

最佳答案

尝试这种方式:

...AND FIND_IN_SET(REL.CountyId, '759,760,761') > 0


在查询中可能看起来像:

...
WHERE  REL.Price  BETWEEN 0 AND 1000000
   AND CAST(REL.M2 AS SIGNED) BETWEEN 0 AND 1000
   AND FIND_IN_SET(REL.CountyId, '759') > 0
   AND FIND_IN_SET(RFR.FilterContentId, filterIds) > 0
ORDER BY REL.Creation;


FIND_IN_SET(str,strlist)


  如果字符串str在由N个子字符串组成的字符串列表strlist中,则返回1到N范围内的值。
  
  如果str不在strlist中或strlist为空字符串,则返回0。


...因此,大于0的数字表示“找到”。

在这种情况下,除非您对列表中的特定位置感兴趣,带in的部分将无用。

10-06 15:06