sql - 计数和内部联接

查看此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