|
|
我有一个名为DETAILS的表，它具有5个数字列DETAILS（id，key2，key3，num1，num2，num3，num4，num5）。id，key2和key3的组合是主键。每个ID可能有多行。( |/ i$ N1 q! }; P) P9 [7 }4 W
我的要求是获取按ID分组的每列的前10个SUM值，如下所示。3 l+ m! ]; m: G. D7 }
select id0 D4 Z6 w; u) }8 c2 m0 Y
,sum(num1) val1/ S4 ]: E0 N2 z4 I' L
from details
7 i* {( j/ a8 d8 t" n1 U4 q0 tgroup by id
% d* `9 `3 N$ m! u" j* corder by sum(num1) desc nulls last. Y9 B) {7 T" v1 z( p6 d# @9 C
limit 10;
$ g& \+ u" e1 l) \; L4 ^select id
. Z ~( Q4 F" S: c ,sum(num2) val2
! k' } T$ _4 X9 ?: o, E# \7 | from details
( q0 [4 H0 `) I: k6 U7 |group by id1 c; E; v2 c2 ?* |0 F9 D- r
order by sum(num2) desc nulls last; a! K+ y# r6 ^7 X$ B/ g3 T% [0 V
limit 10;
) ?3 e/ B- b" e# x' Y8 L' _select id
- J, ~, O6 m8 ?) g: I ]4 t ,sum(num3) val3
7 H; R8 U& Q2 u- V" x% E1 D from details
7 O; c; M2 `; J0 hgroup by id
' U6 S! M2 t" i( morder by sum(num3) desc nulls last
% {0 ]6 E4 u7 [ Y8 nlimit 10;& y+ E* X0 M/ D4 W( R
select id1 t+ o8 n0 ^1 S D
,sum(num4) val4
; N3 ]; `7 ~1 l! b2 f: B4 x u from details
% Q% i/ G/ R' T& y: Q* i |) zgroup by id
5 z" ^" r/ ~' b. x% W/ Vorder by sum(num4) desc nulls last
3 i4 I6 L. K0 _) _limit 10;2 H+ \. C5 u2 i- B
select id8 n% L3 h: H0 s3 Q. i4 L
,sum(num5) val5 t5 ~$ l0 y; R C
from details
$ u8 ~( G) n) t1 H6 X( e2 lgroup by id* s1 }1 v) _$ p
order by sum(num5) desc nulls last
8 S/ j O9 |3 Alimit 10;0 @8 \) n6 A5 D7 \
我需要根据以下ID合并以上结果+ M, y3 }4 ~8 _2 f( `6 C4 U! x$ r5 s
id, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)+ Z8 L; v5 S+ Q8 ?
假设第一个查询返回2 V( j% C% O- t8 x
[{id: 1, val1: 50}, {id: 2, val1: 60}, {id: 3, val1: 70}]
4 @4 ~, A7 m9 l" g- b3 G; V- t1 z: |第二个查询返回
4 x1 ?/ J% u9 ^" a3 P' i[{id: 3, val2: 150}, {id: 4, val2: 160}, {id: 3, val2: 170}]% d* {* a5 u) Y- j5 |- J2 ^
结果应该是" p7 s1 b4 b& t) Z1 z8 l
[
& W* y* A" y( l* r8 W{id: 1, val1: 50, val2: null}, / F# I* Q D8 G5 q/ B- p% {2 X
{id: 2, val1: 60, val2: null}, ( e- o) Q' ]- G1 h$ ~0 [
{id: 3, val1: 70, val2: 150},- | Z5 a% X. b5 V' @' F
{id: 4, val1: null, val2: 160},0 k4 `+ X/ Y: H, c: z# M" ]
{id: 5, val1: null, val2: 170},8 h$ H4 A5 A8 i6 l7 q
]
' _6 v) N/ r" ?. [4 [: }# e& G% r使用join或其他查询进行单一查询是否可行？如果是这样，如何通过优化查询来实现？$ O+ Q/ L% G$ ?" n d9 ?
: S6 Q! f! i4 j0 ~
解决方案: |
|
技术问答
414 人阅读
|
0 人回复
|
2023-09-11
