mysql查询逗号,分隔的多个code连表查询此code对应的描述

博主：牛宝宝
发布时间：2024-01-31 15:20:49
207 次浏览
📦数据库
SQL

需求

pos_menu表中存paycodes有时候为单个code需要连表pccode查询出对应的code描述，直接显示

原SQL

select distinct m.menu,
                bstag.descript                                                 strtag_descript,
                m.name                                                         strconsumename,
                s.name                                                         strsalename,
                m.phone,
                p.descript                                                     strpccode_descript,
                t.descript                                                     strtable_descript,
                m.bdate,
                m.shift,
                m.empno1,
                m.empno3,
                m.invoiceid,
                m.pcrec,
                m.cover,
                m.tables,
                m.amount,
                m.sta,
                syssta.descript                                                strsta_descript,
                pc.descript                                           pc_descript,
                m.paycodes                                       m_paycodes,
                group_concat( ifnull(pc.descript, m.paycodes)   )                             strpaycode_descript,
                m.roomno,
                m.accnt,
                CONCAT(ifnull(m.haccnt_des, ''), '/', ifnull(m.agent_des, ''), '/', ifnull(m.groupno_des, ''), '/',
                       ifnull(m.source_des, ''), '/', ifnull(m.cusno_des, '')) strprotocal,
                m.remark
from pos_menu m
         left join pos_pccode p on m.hotelid = p.hotelid and m.pccode = p.pccode
         left join pos_tblsta t on m.hotelid = t.hotelid and m.pccode = t.pccode and m.tableno = t.tableno
         left join basecode bstag on m.tag = bstag.code and bstag.cat = 'pos_tag' and
                                     (m.hotelid = bstag.hotelid or bstag.hotelid = 'H000014' or
                                      bstag.hotelid = '0571001')
         left join saleid s on (m.hotelid = s.hotelid or s.hotelid = 'H000014' or s.hotelid = '0571001') and
                               m.saleid = s.code
         left join systemcode syssta on m.sta = syssta.code and syssta.cat = 'pos_menu_sta' 
         left join pccode pc on m.paycodes = pc.pccode and pc.cat = 'P' and
                               (pc.hotelid = m.hotelid or pc.hotelid = 'H000014' or pc.hotelid = '0571001')
where 1 = 1
  and m.hotelid = 'H000014' group by menu;

原执行结果

问题

如果pos_menu中存多个paycodes，用逗号隔开，那么直接连表查询则无法查询的到

解决方案

使用 find_in_set()函数，将pos_menu的paycodes多个用逗号连起来的字符串作为set集合，如果set集合里面有paycodes存在于pccode则连表生成一条记录

使用group_concat 函数，将一个set生成的多条记录合并到一个字段，注意合并需要针对唯一id分组，否则所有的记录将合并到一条记录

修改后SQL

select distinct m.menu,
                bstag.descript                                                 strtag_descript,
                m.name                                                         strconsumename,
                s.name                                                         strsalename,
                m.phone,
                p.descript                                                     strpccode_descript,
                t.descript                                                     strtable_descript,
                m.bdate,
                m.shift,
                m.empno1,
                m.empno3,
                m.invoiceid,
                m.pcrec,
                m.cover,
                m.tables,
                m.amount,
                m.sta,
                syssta.descript                                                strsta_descript,
                pc.descript                                           pc_descript,
                m.paycodes                                       m_paycodes,
                group_concat( ifnull(pc.descript, m.paycodes)   )                             strpaycode_descript,
                m.roomno,
                m.accnt,
                CONCAT(ifnull(m.haccnt_des, ''), '/', ifnull(m.agent_des, ''), '/', ifnull(m.groupno_des, ''), '/',
                       ifnull(m.source_des, ''), '/', ifnull(m.cusno_des, '')) strprotocal,
                m.remark
from pos_menu m
         left join pos_pccode p on m.hotelid = p.hotelid and m.pccode = p.pccode
         left join pos_tblsta t on m.hotelid = t.hotelid and m.pccode = t.pccode and m.tableno = t.tableno
         left join basecode bstag on m.tag = bstag.code and bstag.cat = 'pos_tag' and
                                     (m.hotelid = bstag.hotelid or bstag.hotelid = 'H000014' or
                                      bstag.hotelid = '0571001')
         left join saleid s on (m.hotelid = s.hotelid or s.hotelid = 'H000014' or s.hotelid = '0571001') and
                               m.saleid = s.code
         left join systemcode syssta on m.sta = syssta.code and syssta.cat = 'pos_menu_sta'
         left join pccode pc on find_in_set(pc.pccode,m.paycodes)   and pc.cat = 'P' and
                                (pc.hotelid = m.hotelid or pc.hotelid = 'H000014' or pc.hotelid = '0571001') 
where 1 = 1
  and m.hotelid = 'H000014' group by menu;