|
|
我有一个名为DETAILS的表，它具有5个数字列DETAILS（id，key2，key3，num1，num2，num3，num4，num5）。id，key2和key3的组合是主键。每个ID可能有多行。
( E- [3 U; B7 Z& w' h1 m T我的要求是获取按ID分组的每列的前10个SUM值，如下所示。
6 }. I5 F" _! m# w4 F) uselect id
5 ]5 \+ y4 ]- H% t# p7 e# W ,sum(num1) val1
" T" H5 Q* `% X from details
* E7 j. {4 ]: ^group by id$ @8 ]( d; r6 w i% L, M! v+ C
order by sum(num1) desc nulls last5 K* H' s2 N8 R7 o5 {. @$ C `
limit 10;
0 G3 ^% D* c( V. E9 Sselect id
; ]3 k" C. G6 J3 ?0 j ,sum(num2) val2
5 T- H/ D! k3 c: F' D6 L a ? from details
. s& Z' L+ a% n* K+ sgroup by id
2 t# m6 ]0 [8 }% lorder by sum(num2) desc nulls last
' V0 Z) c4 b( r2 Y9 Y2 F- i6 tlimit 10;
$ T( H f2 q f0 e5 _select id
" Q3 b( k' k- R/ P; ] ,sum(num3) val3
- w3 K8 M8 D% c/ X; d/ Q& t' F from details
/ ?, S' q5 h, }. G2 w: b, T/ H0 agroup by id, b+ u; W- J$ B8 R, i8 ~' S: I
order by sum(num3) desc nulls last0 r3 D4 h* z6 J0 _, g
limit 10;
5 D1 M1 o3 U, f' L; ~- u: b6 fselect id
5 R z& v' `- | ,sum(num4) val41 j1 B8 t! w3 \2 M
from details
}& p+ s N b) m$ n2 A, hgroup by id
8 S, O( M% X! S- G- v" `order by sum(num4) desc nulls last
0 l( M* |0 _ }& xlimit 10;
0 t6 |! F4 j) o$ t7 D4 A; o, Gselect id/ G9 n2 E) R2 H& W
,sum(num5) val5! k* {, a }/ x" o. e& F* U0 }
from details
5 P! Z% b3 o0 [6 W4 E# Rgroup by id# _% P# w7 m! Z! S, q. [
order by sum(num5) desc nulls last+ u& n0 \7 M, Y4 Y
limit 10;' I4 e4 m+ ~1 [' e+ y
我需要根据以下ID合并以上结果% ~# D; R; Q" G, P$ Y+ W: l( q# `
id, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)
* v' N. E9 w) x假设第一个查询返回
' d: t7 }$ n$ w& \( T3 i+ r[{id: 1, val1: 50}, {id: 2, val1: 60}, {id: 3, val1: 70}]; i# q+ F+ `# N
第二个查询返回
' A5 ]. c& F0 j" D0 c[{id: 3, val2: 150}, {id: 4, val2: 160}, {id: 3, val2: 170}]
% U/ _ W8 o' v# k: }结果应该是
2 b$ w# H% M4 k/ m- ?/ u6 ~[4 }4 }$ U* e, I8 O4 s V/ d0 q
{id: 1, val1: 50, val2: null},
* u/ O4 K. i+ t+ c{id: 2, val1: 60, val2: null}, ) `- I' ]9 M% X: h! q/ ]
{id: 3, val1: 70, val2: 150},* U1 N+ T/ X, W$ S* C5 j% e$ d
{id: 4, val1: null, val2: 160},
! m$ }" G; P# | v+ O9 h{id: 5, val1: null, val2: 170},3 ^, L9 Y$ ^/ b+ J7 |
]6 B9 x2 A3 U& }
使用join或其他查询进行单一查询是否可行？如果是这样，如何通过优化查询来实现？
2 y$ L A W6 P
+ d: k" [/ a) r7 w* H2 f解决方案: |
|
技术问答
184 人阅读
|
0 人回复
|
2023-09-11
