我有如下三個表：
Video Table id name 1 hello 2 world 3 new
標記表
id name
1 tag1
2 tag2
3 tag3
視頻標簽表
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]：錯誤：用作運算式的子查詢回傳的不止一行
uj5u.com熱心網友回復：
不需要子選擇（并且將回傳多于一行，因此會出現錯誤）。我認為您可以像這樣簡化：
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";
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454922.html
標籤：sql PostgreSQL 子查询