一、原始in子查询
SELECT * FROM basic_zdjbxx WHERE suiji IN ( SELECT zdcode FROM basic_h WHERE zdcode != "" )
该查询语句居然耗时45秒左右的时间,实在无法接收
子查询: SELECT zdcode FROM basic_h WHERE zdcode != "" 只需要约0.05秒查询时间
将子查询结果放到in中,查询语句瞬间出结果
二、解决方法如下:
(1)仍使用in子查询,多查询一次
SELECT * FROM basic_zdjbxx WHERE suiji IN ( SELECT zdcode FROM ( SELECT zdcode FROM basic_h WHERE zdcode != "" ) AS h )
查询速度非常快,毫无延迟
(2)使用LEFT JOIN
SELECT zd.* FROM ( SELECT DISTINCT zdcode FROM basic_h WHERE zdcode != "" ) AS h LEFT JOIN basic_zdjbxx zd ON zd.suiji = h.zdcode
查询速度也非常快,毫无延迟