计数和内连接 [英] Count and inner join

问题描述

看看这个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'

假设我得到了这样的结果:

Let’s say I get a result like this one:

id 
2
1
4
5

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

Now I wanna count how many times the value of these ids appear as value of the column userId in the table erp.orders

例如，如果我有 erp.orders.userId 这是:

For example, if I have erp.orders.userId which is:

userId
2
2
1
4
4
5
5
5

我想要返回这个的请求:

I want the request that is gonna return this:

id  number_of_id
2   2
1   1
4   2
5   3

有什么想法吗?

推荐答案

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

You need to use the count() function and a group by clause. It'll look something like:

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

这篇关于计数和内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！