mysql中的FIND_IN_SET函数
需求:根据省份id,查询可以在该省份下发信息的通道id
sql语句:
SELECT
cpa.CHANNEL_ID id
FROM
channel_price_batch cpa
WHERE
( SELECT FIND_IN_SET('021', cpa.PROVINCE_ID_LIST ) AS a ) > 0//若‘021’在一条记录中存在,则a=1;若在多条记录中存在,有几条a就等于几
#GROUP BY cpa.CHANNEL_ID //把查询结果去重,只显示一条
ORDER BY cpa.CREATE_TIME DESC,cpa.ID DESC //按创建时间降序排序,若创建时间一样,就按id降序排序
项目中用的:
SELECT
c.ID id,
c.NAME name,
c.SP_ID spId,
c.SERVER_IP serverIp,
c.SERVER_PORT serverPort,
c.PROTOCOL_TYPE protocolType,
c.SERVICE_ID serviceId,
c.PROVINCE_ID provinceId,
c.STATUS status
FROM
channel c
left outer join channel_price_batch cpa on cpa.CHANNEL_ID = c.ID
where (SELECT FIND_IN_SET(#{provinceId}, cpa.PROVINCE_ID_LIST) AS a)>0
GROUP BY c.ID //去重
ORDER BY c.CREATE_TIME DESC,c.ID DESC //按创建时间降序排序,若创建时间一样,就按id降序排序
查询结果: