将限制设置为array

小编典典

将限制设置为array_agg（）

sql

我有以下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)}

有任何想法吗？

阅读 234

2021-04-22

共1个答案

小编典典

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

2021-04-22