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

我具有以下表结构：

所有者

OwnerId - PKey
ActionListId - FKey

动作清单

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？

最佳答案

这样尝试

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;