|
|
我有一个名为DETAILS的表，它具有5个数字列DETAILS（id，key2，key3，num1，num2，num3，num4，num5）。id，key2和key3的组合是主键。每个ID可能有多行。, r5 I6 D2 V1 i" j9 ^; t0 q; H
我的要求是获取按ID分组的每列的前10个SUM值，如下所示。$ q6 [# T9 A8 ?7 \' p4 [
select id
/ \8 ~1 ~: C/ d; J1 R# R0 n ,sum(num1) val1
( \: c& ~3 `) Y from details ; G9 P& q% s. `" j$ ?
group by id6 Q2 g; |" \7 M" k6 m
order by sum(num1) desc nulls last
6 w! Q, `3 f7 O! klimit 10;
7 ?, @2 z0 j6 N; X, v* R& T( pselect id
" R- B" I2 B2 D" P- e# V ,sum(num2) val28 }2 G. K; G* K1 z) {$ n
from details , k* @) w# _& S3 d0 T
group by id
! K" A0 `% B% y1 e% i5 Yorder by sum(num2) desc nulls last, T# ~; u9 C- H! d0 z+ P
limit 10;3 T- E0 x6 j9 b
select id
8 b" Z3 _$ i9 ~ ,sum(num3) val39 K) J6 ~4 P% e J2 C
from details * \7 u, S- U6 b# `7 g6 S! t# l0 r: E+ {
group by id
8 y- R4 E8 F. T: U/ i* ?6 korder by sum(num3) desc nulls last
% `: a* ^" y v( [) p/ h7 L' Q, alimit 10;1 n+ H, n! B8 _6 I4 E% h, N2 ?
select id4 j/ m. e. g) M: t
,sum(num4) val4- Y- k5 d. K X+ F
from details ! J8 e! f7 L6 u! X9 W
group by id! e( E, d8 } n7 [0 M1 U5 t% a: T; F
order by sum(num4) desc nulls last& c6 |3 `: J$ t6 K9 Z( H4 @
limit 10;
. }0 _' G* ?' Z% L0 Eselect id7 T% b& s1 D+ m6 N
,sum(num5) val59 W& v/ x- }: d4 u: w
from details
l3 A! Q1 K# l+ f# Mgroup by id
, I* I7 E' ~2 ?3 H: Eorder by sum(num5) desc nulls last, P8 ]) j/ \1 r2 M; X0 `
limit 10;$ x' {/ ^# a. K3 |+ ~, u
我需要根据以下ID合并以上结果
1 z: ^: l, u9 tid, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)
7 j! K+ g' a" G9 h假设第一个查询返回9 G4 v- Z% d6 b" M! c: M
[{id: 1, val1: 50}, {id: 2, val1: 60}, {id: 3, val1: 70}]# C- c( J+ ?8 b: Z
第二个查询返回$ M1 z9 y# @/ _" Z, b: o
[{id: 3, val2: 150}, {id: 4, val2: 160}, {id: 3, val2: 170}]) T, k/ N4 {+ A
结果应该是
3 n" p: ~ a9 y! D' L[
, |5 P3 U. m/ n! T$ p3 o! J9 w: W{id: 1, val1: 50, val2: null}, / u9 b3 ]! `. z/ ]" s: ^
{id: 2, val1: 60, val2: null},
/ ~& S& o, A3 B P{id: 3, val1: 70, val2: 150},
8 h" S4 Y2 M) A j. Q4 ]& c) m{id: 4, val1: null, val2: 160},
# e+ X: J0 ^6 W$ M2 G( a0 O! k{id: 5, val1: null, val2: 170},
. Y9 e8 |, ]# ^3 u# n8 h3 q. {+ Q]
: o# x* K; r4 T1 |9 M使用join或其他查询进行单一查询是否可行？如果是这样，如何通过优化查询来实现？
7 L. J+ K6 `8 g' \8 l/ ~6 D
2 M2 E' H+ X! g, R: N$ |解决方案: |
|
技术问答
467 人阅读
|
0 人回复
|
2023-09-11
