请教一个经典的 postgres 的 sql 怎么写

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 614 天前的主题，其中的信息可能已经有所发展或是发生改变。

现在有 2 张表：student 和 transactions

student 长这样：

id	name
1	a
2	b
3	c

transactions

id	student_id	updated_at
1	1	2024-02-06T16:41:05+08:00
2	1	2024-02-06T13:41:05+08:00
3	2	2024-02-06T12:41:05+08:00

现在想在 postgres 里面用一个 sql ，查出每个用户的 transaction 数量，并且把没有的多一行为 0 。

SELECT s.id AS student_id, s.name, COUNT(t.id) AS transactions_count FROM student s LEFT JOIN transactions t ON s.id = t.student_id GROUP BY s.id, s.name;

但是这样，查出来的结果是

student_id	name	transactions_count
1	a	2
2	b	1

但是我们需要查出的结果是

student_id	name	transactions_count
1	a	2
2	b	1
3	c	0

求教各位高手指点下这个 sql 怎么写。。。

14 条回复 2024-02-07 16:20:58 +08:00

28Sv0ngQfIE7Yloe

2024-02-06 16:54:54 +08:00

奇怪，首先我没用过 pgsql ，但是根据我用 mysql 的经验，我有一个疑问，使用 student 作为左表去 left join 右表，为什么会没有 id =3 的数据呢？

Kerwin24

2024-02-06 17:00:30 +08:00

on 条件不是写错了吗

chanyan

2024-02-06 17:03:52 +08:00

select
t1.id,
t1.name,
count(t2.*) cnt
from
t_student t1
left join
t_transactions t2
on
t1.id = t2.student_id
group by
t1.id, t1.name

dongsuo

2024-02-06 17:03:52 +08:00

SELECT s.id AS student_id, s.name, COALESCE (COUNT(t.id),0) AS transactions_count
FROM student s
LEFT JOIN transactions t ON s.id = t.student_id
GROUP BY s.id, s.name;
网上查到的

ss098

2024-02-06 17:03:53 +08:00

with student_transactions as (
select
transactions.student_id,
count(transactions.id) as transactions_count
from transactions
group by transactions.student_id
)

select
students.id,
students.name,
student_transactions.transactions_count
from students
left join student_transactions on students.id = student_transactions.student_id

Fa11ingWood

2024-02-06 17:05:21 +08:00

SELECT s.id AS student_id, s.name, coalse(COUNT(t.id),0) AS transactions_count
FROM t_ s
LEFT JOIN transactions t ON s.id = t.student_id
GROUP BY s.id, s.name; 试试这样呢

Fa11ingWood

2024-02-06 17:06:15 +08:00

@Fa11ingWood 因为公司现在开发就是用的 pg ，一般这种情况是统计的为 null 然后这条数据就直接不展示了

28Sv0ngQfIE7Yloe

2024-02-06 17:07:24 +08:00

@Morii

我试了下，结果符合预期额，是我理解错了吗？

ss098

2024-02-06 17:09:17 +08:00

刚刚的代码忘记设为 null 了，现在改了一下可以符合预期

with student_transactions as (
select
transactions.student_id,
count(transactions.id) as transactions_count
from transactions
group by transactions.student_id
)

select
students.id,
students.name,
coalesce(student_transactions.transactions_count, 0) as transactions_count
from students
left join student_transactions on students.id = student_transactions.student_id

cookii

2024-02-07 08:50:15 +08:00 via Android

count （ s.id ）就行了

qweruiop

2024-02-07 10:13:18 +08:00

发现一个问题。。。
如果需要列出 2024-02-06T13:41:05+08:00 之后每个学生的交易数量，加上了 where 就不能列出 count 为 0 的行了。。。

```
SELECT s.id AS student_id, s.name, COALESCE(COUNT(t.id),0) AS transactions_count
FROM student s
LEFT JOIN transactions t ON s.id = t.student_id
WHERE t.updated_at>='2024-13-40 00:00:00'
GROUP BY s.id, s.name;
ORDER BY transactions_count DESC
```

出来就只有 1 行了，不加 where ，确实有 3 行。。。再请教下各位高手。。。

| student_id | name | transactions_count |
|------------|------|--------------------|
| 1 | a | 2 |

qweruiop

2024-02-07 10:13:52 +08:00

@Morii @Fa11ingWood @ss098

ss098

2024-02-07 10:53:03 +08:00

@qweruiop 你可以参照我的方案在 student_transactions 中加上 where 条件，可以解决你的需求。

qweruiop

2024-02-07 16:20:58 +08:00

@ss098 好的，谢谢。