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 id="findPageByProvinceId"  resultType="java.util.Map">
  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降序排序

</select>

查询结果：