从多个列的SUM（）中选择并加入postgreSQL

九月相遇

我有一个名为DETAILS的表，它具有5个数字列DETAILS（id，key2，key3，num1，num2，num3，num4，num5）。id，key2和key3的组合是主键。每个ID可能有多行。
我的要求是获取按ID分组的每列的前10个SUM值，如下所示。
* q  d: h9 w: `8 G) {  k' b- Uselect   id
# V- t; C, Q! k        ,sum(num1) val1
. o6 F! z* I6 Y! j/ _& f0 ` from details   
4 W3 N8 A5 {( L7 R* X4 W8 `8 [! kgroup by id
5 u- b5 Y5 e( w7 _* jorder by sum(num1) desc nulls last
limit 10;
select   id
        ,sum(num2) val2
from details  
( a( W+ W. F4 w$ t7 o5 @2 h& D/ K. Tgroup by id
( G! ^) c/ a; N# ~- i6 Z! b8 J% eorder by sum(num2) desc nulls last
limit 10;
9 X: V& f! I# c' v6 Q1 g; \% I# Cselect   id
7 \' K8 Q4 D& N% J% ]        ,sum(num3) val3
" ?& t+ @" T  K from details   
group by id
6 a, k$ _" j6 S6 W% o8 u# Q/ `3 b* Oorder by sum(num3) desc nulls last
limit 10;
select   id
$ b2 ]9 U8 R5 j        ,sum(num4) val4
) A4 m3 h  ]3 o3 ^1 h7 Z; @ from details   
group by id
$ T8 M* Q0 m: E4 Z" }' V% W+ s4 Worder by sum(num4) desc nulls last
limit 10;
8 ^1 I6 Q# [! h1 Rselect   id
: X, J+ J5 t4 r! x        ,sum(num5) val5
from details
group by id
8 s& Z0 i0 E9 `& Y5 b3 V' O5 }. ^order by sum(num5) desc nulls last
; K: {$ c& H. h6 @2 l+ D, Dlimit 10;
4 }: X  d" Z6 e2 Z7 _; i: S我需要根据以下ID合并以上结果
id, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)
假设第一个查询返回
. [1 F! K+ E$ x, n; V[{id: 1, val1: 50}, {id: 2, val1: 60}, {id: 3, val1: 70}]
6 l8 a6 G8 ]5 X3 N' Q9 V第二个查询返回
  {4 k2 h( c1 ?5 k5 V  @' }[{id: 3, val2: 150}, {id: 4, val2: 160}, {id: 3, val2: 170}]
结果应该是
[
{id: 1, val1: 50, val2: null},
* o. m+ M1 W, x5 V- X{id: 2, val1: 60, val2: null},
6 ^& V8 Q; Y7 {& i1 s' K5 Y{id: 3, val1: 70, val2: 150},
{id: 4, val1: null, val2: 160},
( |! a+ q2 U6 {1 |& P  P$ N{id: 5, val1: null, val2: 170},
]
使用join或其他查询进行单一查询是否可行？如果是这样，如何通过优化查询来实现？
$ g9 p: F6 L" @& F               
' c; K+ A4 ]6 F4 M解决方案: