赞
踩
OT IN很好用,但效率很低,共有如下几种方法可以替换,我用几十万数据的测试,最后一种效率是最好的
select email from email where email not in(select distinct email from log);
SELECT email FROM email WHERE not exists (select email from log where log.email=email.email);
SELECT email.email, email.val FROM (SELECT email.*, log.email FROM email LEFT JOIN log ON email.email=log.email) AS res WHERE res.log.email is null;
测试表结构和数据如下
val | |
---|---|
1 | a |
2 | b |
2 | b |
3 | c |
3 | c |
3 | c |
4 | d |
4 | d |
4 | d |
4 | d |
1 |
3 |
3 |
5 |
5 |
5 |
5 |
5 |
运行结果
val | |
---|---|
2 | b |
2 | b |
4 | d |
4 | d |
4 | d |
4 | d |
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。