postgreSQL错误[21000]:错误:用作表达式的子查询返回了多行

Question

我有三个表如下：

视频表id名称1你好2世界3个新

标签表

id   name
1    tag1
2    tag2
3    tag3

videoTag表

videoId    tagId
1          1
2          1
1          2
2          3

我想要的是：

name       tags
hello      tag1, tag2
world      tag1, tag3

因此，我编写了以下查询。

select
string_agg(name, ', ')
from
tag t
join "videoTag" vt on
vt."tagId" = t."id"
group by
vt."videoId"
order by
vt."videoId" 

这给出了以下结果

tags
tag1, tag2
tag1, tag3

我有完整的查询作为：

with videoDetails("name",
"tags") as (
select
v."name" as "name",
(
select
string_agg(name, ', ')
from
tag t
join "videoTag" vt on
vt."tagId" = t."id"
group by
vt."videoId"
order by
vt."videoId" 
) as " tag"
from
video v
join "videoTag" vt on
vt."videoId" = v.id
join tag t on
t.id = vt."tagId"
order by
vt."videoId" 
)
select
*
from
videoDetails;

因此，我得到以下错误：SQL错误[21000]：错误：用作表达式的子查询返回了多行

Answer 1

不需要子选择(并且将返回多行，因此会出现错误(。我认为你可以这样简化：

select
v."name" as "name",
string_agg(t."name", ', ' order by vt."videoId") as "tags"
from
video v
join "videoTag" vt on
vt."videoId" = v.id
join tag t on
t.id = vt."tagId"
group by v."name";