|
我正在尝试在postgresql中创建数据透视表类型视图,并且快要完成了!这是基本查询:+ s! o3 ^( [! v' O9 Q" [3 g5 Z
select
7 x/ s# h: m# {% R( `% d$ xacc2tax_node.acc, tax_node.name, tax_node.rank 8 ]& O. K9 F( D! i: {
from
( i4 h: a' Z2 u4 v' etax_node, acc2tax_node - F+ g. M, ~1 W( ~* s7 J2 W
where
; _2 K- U# ~2 e6 X- @( R6 y5 Ftax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';2 s, _& b3 m$ _
和数据:
5 \+ w* r% n- T7 J4 L acc | name | rank
1 V7 C7 ]# E( J5 x/ b" `) X) @----------+-------------------------+-------------- [7 _ \' S# y7 w6 `# m5 ^2 ^4 ?
AJ012531 | Paromalostomum fusculum | species, E+ v* w6 y+ z
AJ012531 | Paromalostomum | genus1 E5 G4 `5 A9 f/ z. N; V
AJ012531 | Macrostomidae | family
( B; |9 l3 e# }8 l AJ012531 | Macrostomida | order
0 a9 i& C4 q& x3 x; z AJ012531 | Macrostomorpha | no rank
2 Q$ u2 J# z l7 W2 M) P AJ012531 | Turbellaria | class
0 ]5 f% l2 S G! } p. J1 P( c AJ012531 | Platyhelminthes | phylum3 W5 o# T6 Z8 G" f/ P/ d/ ]; M( }
AJ012531 | Acoelomata | no rank$ q6 I$ J3 u G7 y0 a6 X. m
AJ012531 | Bilateria | no rank
" T1 q1 L8 A8 K- ~# v4 Y( I- ~ AJ012531 | Eumetazoa | no rank2 c! r' x5 G9 A: D+ C/ g; A: b
AJ012531 | Metazoa | kingdom8 n4 B( } X0 |* R
AJ012531 | Fungi/Metazoa group | no rank
, g3 P/ @' m" Q( g$ v% p AJ012531 | Eukaryota | superkingdom
7 m. D! q3 V/ }* ?: `! n AJ012531 | cellular organisms | no rank
; M8 |9 o& g* Q$ s0 b我想要得到的是以下内容:/ [% P! t2 u; T5 ~2 n/ r
acc | species | phylum0 q k" h' q9 U& U2 g; I
AJ012531 | Paromalostomum fusculum | Platyhelminthes/ W, S4 Z! K5 ~/ s
我正在尝试使用CASE WHEN进行此操作,因此我得到了以下信息:- B1 ^/ f0 k! r6 ]# m# I, k: \
select
/ w. y$ J3 J+ zacc2tax_node.acc,
2 d: p+ |' l. Z' {7 W) DCASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as species,
9 q* ]; q& |6 `2 u) iCASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as phylum
: S: p8 R! J& \from ' f, }' O- O: r0 M
tax_node, acc2tax_node ! o. t3 o6 Y3 R+ O
where # w$ f2 `" {1 @
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';, V# l0 p. ~4 e }' U4 {6 t. `! \* g
这给了我输出:
6 p& L7 l2 \- @0 q" a acc | species | phylum
n; [+ `/ H3 C# ~8 X+ w----------+-------------------------+-----------------8 g) t+ M) _. }% n8 b/ T
AJ012531 | Paromalostomum fusculum | 5 j$ C/ d, t7 K6 ?+ _7 W' B$ b- p9 p8 Q
AJ012531 | | & A( k, E8 Y+ p+ q7 Y1 Y
AJ012531 | | 1 Z" o, p% t$ r
AJ012531 | |
$ p C3 G$ Q4 b AJ012531 | | . `, h9 X2 q. `( q+ G
AJ012531 | | ! L) z& I; W7 s5 T G
AJ012531 | | Platyhelminthes
/ c0 b: X% R0 Q" ? AJ012531 | | - k; _4 A6 D6 f7 ?
AJ012531 | |
8 n3 z! R/ S c" N+ f/ B3 ~: b AJ012531 | |
. ]8 [0 v( `% F. g# @+ r' S AJ012531 | | f% I! P( k( Y# f! V# t
AJ012531 | | # S. _+ v7 f0 a9 S( x6 q# I* R' }4 C
AJ012531 | |
) R& c2 m; `% a5 ^' S) z. C AJ012531 | |" L' h2 t& X3 c
现在我知道我必须在某个时候按acc分组,所以我尝试3 c( F# c4 u! Q0 l% U# l G6 g
select
" e$ I3 E6 E, K3 `5 F; B' _+ c$ x" Jacc2tax_node.acc, : X' u# \, P; e, r$ i! x* K7 o
CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as sp,
( @; c2 Q. G- f9 ?! r+ Z, U3 S3 E. vCASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as ph / ^! t: U: `/ {! p: H. _3 h$ v' q
from
4 j; z! h6 F. v x4 Atax_node, acc2tax_node
5 h5 b# N4 \ d8 B4 Q7 ~where + t$ h7 V# ^0 R; ^* H" @
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531'
& s3 @: d. r# i9 ^/ X7 Xgroup by acc2tax_node.acc;( S( J, u) Q4 H1 V
但我感到恐惧3 r9 W7 O1 T5 Z; X! G
ERROR: column "tax_node.rank" must appear in the GROUP BY clause or be used in an aggregate function
| G$ Y$ U3 J. t' O我能够找到的所有先前示例在CASE语句周围都使用了SUM()之类的东西,所以我想那是聚合函数。我尝试使用FIRST():
% i; l7 s2 g; gselect
% E% i8 Z s# M; P8 s; `acc2tax_node.acc, % T2 _, s' o; H4 `% \. B4 ~
FIRST(CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END) as sp, - R' R1 P- |3 I& l4 d; R
FIRST(CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END) as ph 9 T+ Q2 B% A" L+ v
from tax_node, acc2tax_node where tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531' group by acc2tax_node.acc;
. X2 v2 o$ W# R+ }& c' o J6 d% G但得到错误:4 q# \" }; \6 Z
ERROR: function first(character varying) does not exist
@$ m5 w7 g8 u. L" X$ h谁能提供任何提示?# }. e( j# g$ F4 G4 S# B
) o( E3 V7 q5 f( z- Q
解决方案:' I! h$ c/ J/ S* @0 r; K7 P# P6 b
% j1 v5 X+ j6 \
% E7 i3 s) _- x9 P3 S; b0 }& L7 I# V9 w! I8 G' W9 J, o
使用MAX()或MIN(),而不是FIRST()。在这种情况下,每个组值在列中将具有所有NULL,但最多只有一个不为null的值。根据定义,这是该组值的MIN和MAX(排除所有空值)。 |
|