mysql - 如何在mysql中用join替换子查询

我有以下查询。

select paytype, sum(amtpaid)
from `tbl_payments`
where locationid = 3  and
      checkstatus not in ('Refund','Tip','Tipadj') and
      paytype not in ('Void', 'Comp') and
      oid in (select oid
              from `tbl_checkout_stage`
              where locationid = 3
              group by oid
              having count(checkid) = sum(checkstatus = 'Paid')
             )
group by paytype

在上述情况下，如何将子查询替换为join。
提前致谢。

最佳答案

试试这个查询

select paytype, sum(amtpaid)
from `tbl_payments`
join (select oid
              from `tbl_checkout_stage`
              where locationid = 3
              group by oid
              having count(checkid) = sum(checkstatus = 'Paid')
             ) a on a.oid=`tbl_payments`.oid
where locationid = 3  and
      checkstatus not in ('Refund','Tip','Tipadj') and
      paytype not in ('Void', 'Comp') and
group by paytype

关于mysql - 如何在mysql中用join替换子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31268262/