欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

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

程序员文章站 2023-12-31 10:09:40
...
原来的子查询

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

回复内容:

原来的子查询

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记录有重复

相关标签: php mysql

上一篇:

下一篇: