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.如果分割符不为','可用 REPLACE(替换str,from_str,to_str)
参考：
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set