Mysql数据库一个表字段中存了id,并以逗号分隔,id对应的详细信息在另一个表中，实现查询的方法...

落日映苍穹つ 2022-01-19 02:41 82阅读 0赞

1，第一种

SELECT `a`.`id`,`a`.`submit_time`,`a`.`house_id`,`a`.`status`,`a`.`type`,`a`.`request`,`a`.`response`,`a`.`expense_customer`,`a`.`expense_company`,
    CONCAT(b.real_name,'(',(select `name` from es_department where id in (trim(BOTH ',' FROM b.dept_id))),')' ) AS sale_name,
    `b`.`phone`,`f`.`node_name` 
    FROM `es_bl_ticket_house` `a` 
    复制代码

2，第二种

SELECT `a`.`id`,`a`.`submit_time`,`a`.`house_id`,`a`.`status`,`a`.`type`,`a`.`request`,`a`.`response`,`a`.`expense_customer`,`a`.`expense_company`,concat_ws('',`b`.`real_name`,'(',d.name,')') AS sale_name,`b`.`phone`,`f`.`node_name` 
    FROM `house` `a` 
    LEFT JOIN `staff` `b` ON `a`.`salement_id`=`b`.`id` 
    LEFT JOIN `department` `d` ON find_in_set(d.id,b.dept_id) 
    LEFT JOIN `main` `h` ON `a`.`house_id`=`h`.`hid` 
    LEFT JOIN `dictionary` `hd` ON `h`.`dictionary_id`=`hd`.`cid`  
    INNER JOIN `workflow_relate_record` `e` ON `e`.`record_id`=`a`.`id` 
    LEFT JOIN `workflow_record` `f` ON `e`.`workflow_record_id`=`f`.`id` 
    WHERE `a`.`status` <> '0'  AND `e`.`module_id` IN (1004,1005) GROUP BY a.id ORDER BY a.submit_time DESC
    复制代码

select u.id,u.name，group_concat(b.名称) from u left join b on find_in_set(b.id,u.部门Id) group by u.id;
    复制代码

转载于:https://juejin.im/post/5c87276bf265da2d914dc96e