SQL查询仅在所有必要列都存在且不为NULL时返回数据
技术问答
119 人阅读
|
0 人回复
|
2023-09-13
|
ID | Type | total
5 p& H; |3 S% _; g: [4 e1 Purchase 12
# n3 ]* J* H- e# F1 Return 2
8 L4 j0 o9 \7 O- w3 d1 Exchange 5
: A* e: Z0 `# I! u0 I9 G; s2 Purchase null
% ?3 w) y B, @5 T$ O1 i* S2 p2 Return 5
7 N8 \* R0 ~9 U2 Exchange 1% b+ [4 @' d i7 g. V4 C
3 Purchase 34
8 \7 @6 }' P; o" ?# t- P3 Return 4
, \/ f6 t/ e% j7 y2 A8 ~3 Exchange 2
0 x$ V+ s7 U& N4 Purchase 12
/ J9 @& G+ l% R% R) W6 P" U4 Exchange 2% x( B% B; e$ k s; j+ }2 |0 @" c
以上是样本数据。我要返回的是:
( a+ \, R9 ^5 s' U; R& NID | Type | total( D0 ?5 M, U: u1 L+ g/ k
1 Purchase 12
# \9 }8 Q! u/ c8 U$ P8 B 1 Return 2
u5 X. ~ Y! R' @! Y 1 Exchange 5
) u# \, x" J: v; r 3 Purchase 343 G z0 b9 _! J1 k( `* w. g) c
3 Return 4
0 S' L6 m) v1 G8 t! N3 b 3 Exchange 2
1 ?3 U/ r/ b& _) L) ]8 J6 Y% h, Z因此,如果该字段的总和为空,或者该ID的Purchase,Return和Exchange的值均不存在,请完全忽略该ID。我该怎么做呢?8 v' W) r. D# L% q
* p$ _$ U; h N) z解决方案:
: G- B8 u! l, @' M! H4 a: I2 n5 W. w
' A2 @+ U9 [7 N7 Z# L. m1 b ~" K( b* U9 H( t% o; g6 R6 |$ h
1 B! W. X" @3 K- F2 M$ N 您可以使用exists。我认为您打算:
; A( V2 \" L P. J8 L& `- U8 P% Fselect t.*5 g1 w( \3 c. e- i4 F% a
from t, w$ F' I8 l8 Q9 ^+ a
where exists (select 1
9 g% M4 h7 F) T e- w9 N from t t2' Y e% H& z4 D' y( E# K v6 F
where t2.id = t.id and t2.type = 'Purchase' and t2.total is not null5 T" [/ v9 Q% B9 O
) and
9 {. J6 N: ?( @- l5 Q/ U% e7 [2 c exists (select 18 W1 N6 g7 P9 Q; w+ A/ L- x
from t t2$ X- N1 A H8 e5 v5 n6 m6 l
where t2.id = t.id and t2.type = 'Exchange' and t2.total is not null+ D# l8 f) S( m3 {( x
) and
8 Y+ N% r/ a$ A, W2 q9 s" F4 M3 [ exists (select 1
# u4 v( g( K) o from t t28 ?1 J6 t& y# r8 r
where t2.id = t.id and t2.type = 'Return' and t2.total is not null! k4 n5 j6 U" ^0 @4 H
);
! r" u6 _8 t( y$ g1 C+ R有一些方法可以“简化”此操作:
/ O2 |2 j7 K1 C6 kselect t.*: K% h: y3 W; h4 O: W9 E- y
from t6 T B; C4 l3 `# b8 d+ a' ^( {
where 3 = (select count(distinct t2.type)/ n& W0 J* ?$ T& y6 g( {: u* s0 k
from t t2
5 t! a) n: u; S* O& U: b where t2.id = t.id and$ R; d) D: M3 S" M' V5 l, z l- p
t2.type in ('Purchase', 'Exchange', 'Return') and
9 L. F8 |$ B1 d, i* L t2.total is not null
+ X& s7 V( c @4 B' B/ b* d& W ); |
|
|
|
|
|