|
|
我有一个名为DETAILS的表，它具有5个数字列DETAILS（id，key2，key3，num1，num2，num3，num4，num5）。id，key2和key3的组合是主键。每个ID可能有多行。
" @% O8 n; n1 I6 e ]8 X9 ]我的要求是获取按ID分组的每列的前10个SUM值，如下所示。
9 |/ v7 V- d1 n- ?2 N" k* w1 \- kselect id8 Z' v0 F$ l5 l
,sum(num1) val18 c" {& x: R' u/ h- o0 d
from details / I" k! I5 M2 c: }, V, N
group by id7 W1 O ~1 a* C8 F( V& {
order by sum(num1) desc nulls last( q4 b0 E! x, f# Y) |! ]2 ~; e2 R
limit 10;
# o9 ^$ D5 m1 _) Bselect id
) {& h+ \3 c; f$ e) T ,sum(num2) val2- p$ w4 ]/ G& X: D4 r$ l
from details
/ f8 S& \) n) g: agroup by id0 p; ?8 ]! w" P/ z
order by sum(num2) desc nulls last
( r, y; I+ s0 }5 E% A9 c: Xlimit 10;7 @" x. F7 n! W, F& n9 ^
select id. Y0 X5 _9 m3 s
,sum(num3) val37 V9 S- R5 P3 ^* W3 e
from details i1 R, ], d6 Q0 Y5 X
group by id$ B/ J0 f |( j1 a6 R1 ^
order by sum(num3) desc nulls last9 ?$ }- `/ B) s/ k* A7 P4 G
limit 10;
! E7 V2 a9 S* ~: cselect id5 ^8 L* C9 A# q: f
,sum(num4) val4
4 f$ \ ~* x F- q6 a" K from details * u3 ^0 b& S+ x' O/ B0 [
group by id
& A6 M# B% N7 Uorder by sum(num4) desc nulls last# ^! i/ ^% |4 ~+ `+ R! w7 w+ U( X
limit 10;& X( _. v0 v4 a8 Q5 O
select id
) N% C' h2 z& Z7 r7 Z( n- t7 \ ,sum(num5) val5
( e/ B4 Z; U0 q8 |% l from details
( W" P$ c ^& D4 K) D3 n( agroup by id' u# n5 N$ l. r* V
order by sum(num5) desc nulls last" t2 N: K4 A6 \- e7 c! A
limit 10;6 o9 I9 r" E1 }6 U1 R. Y
我需要根据以下ID合并以上结果8 v+ g2 `0 |- b" ?# T L
id, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)/ R% k! W5 k u0 \9 x6 p5 J
假设第一个查询返回1 {5 j& w2 C3 t$ [& e1 J1 X
[{id: 1, val1: 50}, {id: 2, val1: 60}, {id: 3, val1: 70}]( t4 I' l( w, T, C, X U
第二个查询返回
! M& r7 M f/ h7 s" u' x7 k[{id: 3, val2: 150}, {id: 4, val2: 160}, {id: 3, val2: 170}]
. }# [/ a; c" ^; U结果应该是4 _. g( b( c1 W
[
: _+ _2 s1 F. l4 l{id: 1, val1: 50, val2: null}, 2 F$ a: {) e% F j- V: I, S
{id: 2, val1: 60, val2: null}, 9 e% `" ?+ B: R) }
{id: 3, val1: 70, val2: 150},
' I! B+ P$ i' d" y3 C" s{id: 4, val1: null, val2: 160},
" E( A7 d; a5 q# A5 I0 t{id: 5, val1: null, val2: 170},
' @: a8 N* s( W3 n3 a W]
( s! u2 P- Y: a% |# `% S& }* l2 A8 }" \使用join或其他查询进行单一查询是否可行？如果是这样，如何通过优化查询来实现？! L3 j6 j' w( w% h. R& M
+ T3 j- [7 E& i( Q" A: b
解决方案: |
|
技术问答
466 人阅读
|
0 人回复
|
2023-09-11
