mysql - 连接具有相同 FK 的两个字符串

Question

我在mysql中有以下表

table payment_enum(
id int PK,
name text,
category_id int FK to category
)

table category(
id int PK,
category_name text
)

我想要的是:在 payment_enum 中，可能有两列名称不同但具有相同的category_id例如:

select * from payment_enum 
pk   name  category_id
1    'first'  2
2    'second' 2

PK = 2 的类别名称为“笔记本电脑”是否可以使用将产生以下结果的查询

category_name category_PK enum_names
laptops        2            first,second 

伪查询

select c.name,c.pk, e.name as (concat (e.name,concat(',',select e2.name where e2.name like e.name from payment_enum as e2)))
 from category as c and payment_enum as e

Answer 1

试试这个:

select c.name as category_name ,c.pk as category_PK , GROUP_CONCAT(p.name) as enum_names from
(select * from category) as c
left JOIN
(select * from payment_enum) as p
on c.id = p.category_id GROUP BY  c.name,c.pk