MYSQL:仅检索具有特定数据字段的用户

Question

我有三个表:Order、Payment 和 User。

我试图仅查找使用特定付款方式(现金)且未使用任何其他付款方式的用户。

我构建的简单查询如下所示:

select 
user.id,
user.fname,
user.lname,
user.email
from `order` as o
inner join payments on o.id = payments.order_id
inner join user on o.user_id = user.id
where o.orderplaced_ts > "2016-08-01 00:00:00"
and o.store_id = 6
and o.order_status != "Cancelled"
and payments.payment_method = "Cash"
group by user.id

但是，如果用户在该时间范围内至少使用一次现金作为付款方式，则会返回用户。如何限制只能使用现金而不能使用其他付款方式？

我是否应该为每种付款方式设置多个子查询，然后将每个返回与其他返回进行比较以消除越来越多的用户？

Answer 1

您可以使用不在user.id中的其他方法

  select 
  user.id,
  user.fname,
  user.lname,
  user.email
  from `order` as o
  inner join payments on o.id = payments.order_id
  inner join user on o.user_id = user.id
  where o.orderplaced_ts > "2016-08-01 00:00:00"
  and o.store_id = 6
  and o.order_status != "Cancelled"
  and payments.payment_method = "Cash"
  and user.id not in (select distinct o.user_id 
                      from `order`  
                      inner join  payments on o.id = payments.order_id 
                      where payments.payment_method <> "Cash" )
  group by user.id