时间戳

首页 > 解决方案 > 如何选择整数和数组元素？

sql - 如何选择整数和数组元素？

问题描述

我可以将“col3”中的重复值选择到一个数组中，并使用以下命令按“col1”、“col2”排列它们：

select "col1", "col2", array_agg("col3"  order by "col1", "col2") as myArray FROM 
     myTable group by "col1", "col2"

接下来，我想选择数组的前两个元素（因此它们出现在自己的列中）并通过执行以下操作继续排列它们“col1”、“col2”：

select "col1", "col2", element[1], element[2] from 
    (select "col1", "col2", array_agg("col3"  order by "col1", "col2") as myArray FROM 
         myTable group by "col1", "col2") as  vv(element)

但是，我收到以下错误：

ERROR:  cannot subscript type integer because it is not an array

有没有办法转换“col1”和“col2”的整数值，以便我可以将数组元素放入由“col1”和“col2”组织的单独列中？

标签： sqlarrayspostgresqlalias

解决方案

您的表别名vv(element)错误，因为内部查询有 3 列，而不仅仅是 1 列。通过仅指定一列，您只需重命名第一列（col1to element），而其他两列保留其原始名称来自内部查询。

因此，您的别名需要为所有三列提供名称：

select "col1", "col2", element[1], element[2] 
from (
   select "col1", "col2", array_agg("col3"  order by "col1", "col2") as myArray 
   FROM myTable 
   group by "col1", "col2"
) as vv(co1, col2, element);

vv(co1, col2, element)本质上重命名myarray为element.

由于内部查询中的所有列都有正确的名称，因此您实际上不需要表别名中的列别名：

select "col1", "col2", myarray[1] as element_one, myarray[2]  as element_two
from (
   select "col1", "col2", array_agg("col3"  order by "col1", "col2") as myArray 
   FROM myTable 
   group by "col1", "col2"
) as vv;

推荐阅读