gpt4 book ai didi

sql - 计数和内连接

转载 作者:行者123 更新时间:2023-11-29 11:58:47 24 4
gpt4 key购买 nike

看看这个sql请求:

select distinct erp.users.id 
from erp.users
inner join prod.referral_order_delivered
on erp.users.id= prod.referral_order_delivered.user_id::uuid
inner join erp.orders
on erp.orders."userId"::uuid= erp.users.id
where
"paidAt"::date >= '2016-06-07'
and "paidAt"::date <= '2017-07-07'

假设我得到这样的结果:

id 
2
1
4
5

现在我想计算这些 id 的值作为 erp.orders 表中 userId 列的值出现了多少次

例如,如果我有 erp.orders.userId,它是:

userId
2
2
1
4
4
5
5
5

我想要返回这个的请求:

id  number_of_id
2 2
1 1
4 2
5 3

有什么想法吗?

最佳答案

您需要使用 count() 函数和 group by 子句。它看起来像:

select 
erp.users.id
, count(1)
from
erp.users
inner join prod.referral_order_delivered
on erp.users.id = prod.referral_order_delivered.user_id::uuid
inner join erp.orders
on erp.orders."userId"::uuid = erp.users.id
where
"paidAt"::date >= '2016-06-07'
and "paidAt"::date <= '2017-07-07'
group by
erp.users.id

关于sql - 计数和内连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44975464/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com