mysql - 想要使用另一个表的列值的 'in'语句拉取所有记录

Question

我有以下表结构:

所有者

OwnerId - PKey  
ActionListId - FKey

Action 列表

ActionListId - PKey  
Actions - VARCHAR(8000)

操作

ActionId - PKey

ActionList.Actions的格式如下:

ActionId1, ActionId2, ActionId3, ActionId4, ... ActionIdN

我正在尝试执行此查询:

select 
    o.*,
    a.Name
from Owner as o
join ActionList as al on al.ActionListId = o.ActionListId
join Action as a on a.ActionId in (al.Actions)
where o.OwnerId = 1;

但我拿回了一条记录。为什么第二次连接没有成功连接整个数组，而是仅连接第一个元素？有没有办法通过单个查询获取 ActionList 中的所有 Action？

Answer 1

尝试这样

SELECT O.*,A.Name
FROM Owner AS O
     LEFT JOIN ActionList AS AL ON 
               AL.ActionListId = O.ActionListId
     LEFT JOIN Action AS A ON 
               A.ACtionID IN (SELECT ACTIONS FROM ACTIONList)
WGERE O.OwnerId = 1;