mysql 多个ID list_mysql 一个字段存多个id,关联查询

mysql订单表中一个字段对应多个优惠卷id，用','隔开。

财务需要优惠卷使用情况

刚开始用优惠卷id(长度固定，不会出现特殊情况) like 订单表关联id字段 查询，使用'||'拼接'%'。

发现无论是在优惠卷id或订单管理字段加'%'都不能打到要求,最后使用find_in_set()方法 成功实现。

代码如下：

select cou.cp_id as '优惠卷id'

,cou.cp_no as '卷号'

,cast(case cou.generate_type  when 0 then '生成'  when 1 then '发放' else cou.generate_type

end AS char(20)) as '生成类型'

,cast(case cou.state when 0 then '待领用' when 1 then '未使用' when 2 then '已使用' when 3 then '已过期' when 4 then '已失效' else cou.state end as char(20)) as '状态'

,cast(case cou.is_gift when 0 then '否' when 1 then '是' else '' end as char(20))as '是否礼包领取'

,cast(case cou.cp_type when 1 then '现金券' when 2 then '折扣券' when 3 then '满减券' when 4 then '买赠券' when 5 then '福利券' when 6 then '礼包券' else cou.cp_type end as char(20)) as '类型'

,cast(case cou.pro_property when 0 then '社区通用' when 1 then '商家专用' when 2 then '内容通用' when 3 then '礼包发放' else cou.pro_property end as char(20)) as '卷属性'

,cou.start_date as '开始时间'

,cou.end_date as '结束时间'

,cou.discount as '折扣'

,cou.given_number as '赠送数量'

,cou.buy_number as '购买数量'

,cast(case cou.is_limit when 0 then '否' when 1 then '是' else cou.is_limit end as char(20) )as '是否限额'

,cou.preferential_price as '优惠金额'

,cou.seller_id as '商家id'

,crm.seller_name as '商家'

,cou.user_id as '业户id'

,oinfo.owner_name as '业户'

,pro.pro_title as '活动标题'

,cast(case pro.cp_type when 1 then '现金券' when 2 then '折扣券' when 3 then '满减券' when 4 then '买赠券' when 5 then '福利券' else pro.cp_type end as char(20)) as '活动类型'

,o.create_userid as '订单用户id'

,u.owner_name as '订单用户'

,o.create_date as '下单时间'

,case o.module_type when 1 then '邻聚街' when 2 then '精选商品' when 3 then '限时购' when 4 then '团购' when 6 then '到家服务' when 7 then '普通商品' end as '订单类型'

,o.total_money as '订单金额'

,c.seller_name as '商家名称'

,case o.order_status when 3 then '已完成' when 9 then '已关闭' when 7 then '待收货' when 1 then '待处理' when 2 then '处理中' when 4 then '已取消' when 5 then '待付款' when 6 then '待发货' when 8 then '待收款' end as '订单状态'

,case t.as_state when 1 then '申请中' when 2 then '已拒绝' when 3 then '已通过' when 4 then '已撤销' when 5 then '已完成' end as '退款状态'

,t.refund_amount as '退款金额'

,o.coupons_id as '优惠卷id'

,o.coupons_money as '优惠卷金额'

from  (tbc_coupons cou,tbg_order_record o)

left join  tbo_owner_info_new oinfo on cou.user_id=oinfo.ownerinfo_id

left join crm_manage_seller crm on cou.seller_id=crm.seller_id

left join tbc_promotion pro on cou.pro_id=pro.pro_id

left JOIN tbo_owner_info_new u on o.create_userid= u.ownerinfo_id

left JOIN crm_manage_seller c on o.seller_id=c.seller_id

left JOIN tbg_after_sale t on  o.as_id=t.as_id

where cou.pro_id is not null

and cou.end_date>'2015-12-1'

and find_in_set(cou.cp_id,o.coupons_id)

and o.operate_date>= '2015-12-1'

and o.operate_date< '2015-12-30'

order by cou.seller_id,cou.cp_type

核心代码：

select * from a,b where find_in_set(a.id,b.aids);

find_in_set(str,strlist)方法返回查询查询到的str在strlist的位置(从1开始),为找到返回0.如果分割符不为','可用替换

参考：

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set