sql - 如何在Postgresql中的array_agg列上加入 View ？

我正在搜索语法以将视图与array_agg列连接起来。
视图：

CREATE VIEW sampleview AS
SELECT groupid, array_agg(akey) AS keyarray
FROM sampletable
GROUP BY groupid;

现在我想做这样的事：

SELECT s.groupid, a.somedata
FROM sampleview AS s
JOIN anothertable AS a ON a.akey IN s.keyarray;

错误消息：

ERROR:  syntax error at or near "s"
LINE 3: JOIN anothertable AS a ON a.akey IN s.keyarray;

语法错误（很可能）或不可能。我不认为这是不可能的，但如果是我要求的另一个选择。
上面伪查询的预期输出（带有测试代码）：

 groupid | somedata
---------+----------
       1 | foo
       1 | bar
       2 | monkey
(3 rows)

测试代码：

CREATE TABLE sampletable (groupid int not null, akey int not null);
CREATE TABLE anothertable (akey int not null, somedata varchar(20));
CREATE VIEW sampleview AS SELECT groupid, array_agg(akey) AS keyarray FROM sampletable GROUP BY groupid;
INSERT INTO sampletable VALUES(1,20),(1,22),(2,33);
INSERT INTO anothertable VALUES(20, 'foo'),(22,'bar'),(33,'monkey');

最佳答案

SELECT s.groupid, a.somedata
FROM sampleview AS s
JOIN anothertable AS a ON a.akey = any(s.keyarray);

关于sql - 如何在Postgresql中的array_agg列上加入 View ？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17971164/