Using values in an array in a subquery
От | David Orme |
---|---|
Тема | Using values in an array in a subquery |
Дата | |
Msg-id | 326D1DF0-C8BB-4C85-8132-5B3478B7006A@ic.ac.uk обсуждение исходный текст |
Ответы |
Re: Using values in an array in a subquery
|
Список | pgsql-novice |
Hi, Can anyone give some pointers on how to construct a query to do the following: I have a table like this: create temp table array_test (node integer, members integer[], var numeric); insert into array_test values (1, NULL, 1); insert into array_test values (2, NULL, 2); insert into array_test values (3, NULL, 3); insert into array_test values (4, NULL, 4); insert into array_test values (-3, '{1,2}', 5); insert into array_test values (-2, '{-3,3}', 6); insert into array_test values (-1, '{-2,4}',7); test=# select * from array_test; node | members | var ------+---------+----- 1 | | 1 2 | | 2 3 | | 3 4 | | 4 -3 | {1,2} | 5 -2 | {-3,3} | 6 -1 | {-2,4} | 7 (7 rows) I want to be able to aggregate a value for each row based on the values of var for the rows with node values appearing in the members array. If the aggregate was sum, then I'm looking to get something like this: node | sum -----+----- 1 | 2 | 3 | 4 | -3 | 3 -2 | 8 -1 | 10 I've been trying with syntax as below but with no success. select node, sum(var) from array_test a1 where node = any( cast((select members from array_test a2 where a1.node = a2.node) as integer[])) group by node; Any help would be much appreciated, David
В списке pgsql-novice по дате отправления: