将限制设置为array_agg() [英] Set limit to array_agg()

问题描述

我有以下Postgres查询:

I have the following Postgres query:

SELECT array_agg("Esns".id ) 
FROM public."Esns", 
     public."PurchaseOrderItems" 
WHERE 
    "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND "PurchaseOrderItems"."GradeId"=2 
LIMIT 2;

该限制将影响行.我希望它将array_agg()限制为2个项目.下面的查询有效，但是我得到的输出是用引号引起来的:

The limit will affect the rows. I want it to limit the array_agg() to 2 items. The following query works but I get my output with each entry in quotes:

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

或者如果您希望将输出作为数组，则可以切片它:

or if you want the output as array you can slice it:

SELECT (array_agg("Esns".id ))[1:2] as id_array
FROM public."Esns", 
     public."PurchaseOrderItems" 
WHERE 
    "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND "PurchaseOrderItems"."GradeId"=2 

这篇关于将限制设置为array_agg()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！