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

原来的子查询

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

执行结果是本@文来源[email protected]搞@^&代*@码网(搞代gaodaima码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

回复内容：

原来的子查询

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

sdb_invite_invite.in_member_id记录有重复

喜欢 (0)赏

[搞代码]

分享 (0)