MySql:FIND_IN_SET无法正常工作 [英] MySql: FIND_IN_SET does not work properly

问题描述

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

I have a stored procedure which has a string parameter. This parameter comes seperated, as '759,760,761'. In where clause I want to use it like this.

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

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

But there is no returned data however there is row with 759 county id. Datas come if I delete this clause.

这是我所有的sql代码:

Here is my all sql code:

    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

查询中的内容可能类似于:

in query might look like:

...
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范围内的值.

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

如果str不在strlist中或strlist为空字符串，则返回0.

Returns 0 if str is not in strlist or if strlist is the empty string.

...因此，大于 0 的数字表示已找到".

...so, number greater than 0 means "found".

带有 in 的零件将无用.

这篇关于MySql:FIND_IN_SET无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！