选择计数、内部联接和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年间支付的ids中，还有表prod.referral_order_delivered中的ids，我想计算一下它们在表erp.orders中总共出现了多少次。在我目前的请求中，我只计算2017-06-07至2017-07-07.

我想要的结果是：

userId  count
1       2
2       4

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

Answer 1

试着解决你的问题。你想：

在2017-06-07和2017-07-07年间支付的in，这些in在表prod.referral_order_delivered中

和

计算它们在表erp.orders中总共出现了多少次

你已经有了前者：

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"

Answer 2

不如：

     SELECT erp.orders."userId" 
           ,count(erp.orders."paidAt")
       FROM erp.orders
 INNER JOIN prod.referral_order_delivered
         ON erp.orders."userId"::uuid = prod.referral_order_delivered.user_id::uuid
        AND erp.orders."paidAt"::date >= '2017-06-07'
        AND erp.orders."paidAt"::date <= '2017-07-07'
   GROUP BY erp.orders."userId"

Answer 3

使用having子句而不是where子句：

select o."userId", count(o."userId")
from erp.orders o inner join
     prod.referral_order_delivered rod
     on o."userId"::uuid = rod.user_id::uuid
group by o."userId"
having sum( (o."paidAt"::date >= '2017-06-07' and o."paidAt"::date <= '2017-07-07')::int) > 0;