PostgreSQL中的GROUP BY和COUNT
技术问答
94 人阅读
|
0 人回复
|
2023-09-13
|
查询:3 }8 ^5 L0 V( {. h$ k
SELECT COUNT(*) as count_all, 0 v2 M; c6 Y% o6 w4 q
posts.id as post_id ; Z$ m8 t4 z5 a
FROM posts ' H) H0 x- f4 u
INNER JOIN votes ON votes.post_id = posts.id
. W7 ^: P7 i. F. ?1 j3 `GROUP BY posts.id;% e7 Z# V4 a& y, O3 `1 a8 U& B
返回nPostgresql中的记录:
8 n8 u0 L2 v: I g# u0 L. E# { count_all | post_id( A1 H0 H$ I/ l6 @0 Y2 ?/ U" \
-----------+---------
. c8 h( k o/ n6 D' u, R 1 | 6
# |9 \4 o9 G. G 3 | 4" m5 n& u* e. c
3 | 5. K* m! @$ r/ j, q1 v1 T
3 | 19 [3 k1 {# ~1 D& U7 n0 B3 S
1 | 9: `. k+ B9 L* p- j5 Z: T: d
1 | 106 ~$ v- a$ n( j1 P r7 V8 n* G
(6 rows)
: z; u# C1 q0 [/ D: R s0 [我只想检索返回的记录数:6。; Q; s" ?/ M" Y0 R% s5 ?
我使用了一个子查询来实现我想要的,但这似乎不是最佳的:
4 F W! r( r9 J k8 JSELECT COUNT(*) FROM (
9 E6 n" [% |3 E) V SELECT COUNT(*) as count_all, posts.id as post_id ) g, H f" \3 D( s* _; v2 H+ s
FROM posts
7 F, N) v9 m% b* L8 n INNER JOIN votes ON votes.post_id = posts.id
& n2 H. H7 z7 L. o! ? GROUP BY posts.id7 V, T) W n9 e3 s3 O
) as x;
4 P- L& ^ x: O( W% l( ?如何在PostgreSQL中获得这种上下文中的记录数?
, e% I0 ^' H( H( {5 y ' D( {5 K0 }" S: c L
解决方案: |
|
|
|
|
|