将子查询改造成join连接查询

Question

原来的子查询

SELECT COUNT(*) AS tp_count FROM sdb_b2c_orders WHERE pay_status='1' and createtime>1413533130 and area_code in (1030,1031,1032,1033) and member_id in (select member_id from sdb_invite_invite where in_member_id=14273 or in_member_id=13742 or in_member_id=14299) LIMIT 1

执行结果是22

使用连接查询代替上面的子查询

SELECT COUNT(*) AS tp_count FROM sdb_b2c_orders AS bo INNER JOIN sdb_invite_invite AS ii ON bo.member_id=ii.in_member_id WHERE bo.pay_status='1' AND bo.area_code IN (1030,1031,1032,1033) AND bo.createtime>1413533130 AND ii.in_member_id IN (14273,13742,14299)

执行结果却是131

Answer 1

sdb_invite_invite.in_member_id记录有重复