## 现有表good如下
id num
a 5
b 10
c 15
c 10
e 15
a 10
b 15
d 10
a 5
d 10
c 5
语句1 select id from good where num <>5 结果1是:b,c,c,e,a,b,d,d
语句2 select id from good where num =5 结果2是:a,a,c我想实现联合查询结果1里去掉重复的结果2
结果3是b,e,d
然后根据结果3 select * from good where id=结果3最后输出
id num
b 10
e 15
d 10
id num
a 5
b 10
c 15
c 10
e 15
a 10
b 15
d 10
a 5
d 10
c 5
语句1 select id from good where num <>5 结果1是:b,c,c,e,a,b,d,d
语句2 select id from good where num =5 结果2是:a,a,c我想实现联合查询结果1里去掉重复的结果2
结果3是b,e,d
然后根据结果3 select * from good where id=结果3最后输出
id num
b 10
e 15
d 10
from good A
where num<>5 and not exists(select 1 from good B where A.id=B.id and B.num=5)
服务器直接当机。。cpu被mysql占用100%
SELECT * FROM TT A WHERE NOT EXISTS(SELECT 1 FROM TT WHERE A.ID=ID AND NUM=5)
在ID上建立索引,如果NUM只有3种结果,NUM没有必要在ID、NUM上建立复合索引
---临时表1CREATE TABLE tmp_t1 (
id VARCHAR(50) NOT NULL ) ENGINE = MEMORY ;
CREATE INDEX index1 ON tmp_t1 (id);INSERT INTO tmp_t1(id) SELECT id FROM good WHERE num <>5
---临时表2CREATE TABLE tmp_t2 (
id VARCHAR(50) NOT NULL ) ENGINE = MEMORY ;
CREATE INDEX index2 ON tmp_t2 (id);
INSERT INTO tmp_t2(id) SELECT id FROM good WHERE num =5
SELECT a.id FROM tmp_t1 AS a LEFT JOIN tmp_t2 AS b ON a.id=b.id WHERE b.id IS null