我有以下Postgres查询：
SELECT array_agg("Esns".id )
FROM public."Esns",public."PurchaSEOrderItems"
WHERE
"Esns"."PurchaSEOrderItemId" = "PurchaSEOrderItems".id
AND "PurchaSEOrderItems"."GradeId"=2
LIMIT 2;
限制将影响行.我希望它将array_agg()限制为2个项目.以下查询有效,但我在输出中使用引号中的每个条目：
SELECT array_agg ("temp")
FROM (
SELECT "Esns".id
FROM public."Esns",public."PurchaSEOrderItems"
WHERE
"Esns"."PurchaSEOrderItemId" = "PurchaSEOrderItems".id
AND "PurchaSEOrderItems"."GradeId"=2
LIMIT 4
) as "temp" ;
这给了我以下输出
{(13),(14),(15),(12)}
有任何想法吗？
解决方法
select id[1],id[2]
from (
SELECT array_agg("Esns".id ) as id
FROM public."Esns",public."PurchaSEOrderItems"
WHERE
"Esns"."PurchaSEOrderItemId" = "PurchaSEOrderItems".id
AND "PurchaSEOrderItems"."GradeId"=2
) s
或者如果您希望输出为数组：
SELECT (array_agg("Esns".id ))[1:2] as id_array
FROM public."Esns",public."PurchaSEOrderItems"
WHERE
"Esns"."PurchaSEOrderItemId" = "PurchaSEOrderItems".id
AND "PurchaSEOrderItems"."GradeId"=2
