我有一个带有字符串参数的存储过程。此参数以'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
的部分将无用。