有如下语句,想把他转换成exists语句:外表内表用的都是同一张表

select * from shuxue_new as a where id in  (select max(id) from shuxue_new group by userid);

select * from shuxue_new as a where id not in  (select max(id) from shuxue_new group by userid);


于是:

select * from shuxue_new as a where exists  (select max(id),userid,count(*) from shuxue_new as b where a.id=b.id  group by userid);

select * from shuxue_new as a where not exists  (select max(id),userid,count(*) from shuxue_new as b where a.id=b.id  group by userid);


结果很悲剧,不管怎么调整exists子句,结果依旧不是正确的!原因是因为上面exists子句中有group by!

而在一条SQL语句执行过程中,where条件是在group by与选取select行前面执行的,所以上面的exists子句在还没分组前已经执行了a.id=b.id,因为是同一张表,所以a.id=b.id肯定是返回true的!

另外像select max(id),userid,count(*) from shuxue_new as b where a.id=b.id( 或者等于一个常量) group by userid) 这种语句本身就有问题!因为where条件里指定了常量等条件,你再group by ,再select max(id) 已经是没有意义的了!最后查询出的永远都只是符合where条件的那几列!


所以像这种使用了group by,或者select max()等函数的in 语句是不能方便的转换成existsy语句的!!



delete a from shuxue_new as a left join (select max(id) as id from shuxue_new  group by userid) as b on a.id=b.id where b.id is null;