sql - 选择计数、内部连接和 where 语句

Question

这是 erp.orders:

userId  paidAt
1       2017-06-30
1       2017-06-18
2       2017-06-07
4       2017-06-07
3       2017-01-01
2       2017-01-01
2       2017-01-01
2       2017-01-01

这是 prod.referral_order_delivered

user_id
1
2

这个请求:

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

会给我这个结果:

userId  count
1       2
2       1

这不是我想要的结果。在 2017-06-07 和 2017-07-07 之间支付的并且在表 prod.referral_order_delivered 中的 id 中，我想计算它们在表 erp.orders 中出现的次数总共。在我当前的请求中，我只计算从 2017-06-07 到 2017-07-07。

我想要的结果是:

userId  count
1       2
2       4

查看如何排除 userId 3 和 4。这真的很重要。你能修改我的请求吗？

Answer 1

尝试拆分您的问题。你想要:

the ids that paid between 2017-06-07 and 2017-07-07 and that are in the table prod.referral_order_delivered

和

to count how many times they appear in the table erp.orders IN TOTAL

你已经有了前者:

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

而后者只是简单地使用了一个内部选择:

select erp.orders."userId", count(erp.orders."userId")
from erp.orders
where
   erp.orders."userId" in (select ...)
group by erp.orders."userId"