MySQL查询获取与主从表分隔的值逗号 [英] MySQL query get value comma separated from master detail table

问题描述

我在 mysql 数据库中有 2 个表:

I've 2 tables in mysql database:

tbl_fruit(fruit_id，fruit_name):

tbl_fruit (fruit_id, fruit_name):

fruit id   |   fruit_name
--------------------------
  1        |     Apple  
  2        |     Banana  
  3        |     Mango 

tbl_order(order_id、order_name、fruit_id):

tbl_order (order_id, order_name, fruit_id):

  order_id  |  order_name  | fruit_id 
  ----------------------------------- 
  1         |   John       |   1,2  
  2         |   Matt       |   1,3  
  3         |   Jessica    |   1,2,3  

预期输出:

order_name      |  selected_fruit  
----------------------------------
John            |   Apple, Banana  
Matt            |   Apple, Mango  
Jessica         |   Apple, Banana, Mango  

我尝试了一些查询，但与预期的输出不匹配.提前致谢.

I've tried some queries but did not match as expected output. thanks in advance.

已解决:

SELECT 
  order_name,
  (select 
    GROUP_CONCAT(fruit_name SEPARATOR ', ') 
   from tbl_fruits as fru 
   where
     FIND_IN_SET (fru.fruit_id,ord.fruit_id)) as selected_fruit 
     FROM `tbl_order` as ord   

http://sqlfiddle.com/#!9/08eb6f/6/0

推荐答案

好吧，你可以做到，但这并不意味着你应该这样做.您可以在子查询中使用 find_in_set() 加入这两个表，然后在外部查询中使用 group_concat() 来获取结果:

Well, you can do it, but it does not mean that you should. You can join the 2 tables using find_in_set() in a subquery and then use group_concat() in the outer query to get the results back:

select t.order_name, group_concat(t.fruit_name) as selected_fruits
from
(select o.order_name, f.fruit_name
from `order` o inner join fruit f on find_in_set(f.fruit_id, o.fruit_id)) t
group by t.order_name

这篇关于MySQL查询获取与主从表分隔的值逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！