sql语句之IN&&EXISTS
来源:互联网 发布:liguo矩阵怎么使用 编辑:程序博客网 时间:2024/05/17 03:16
NOT IN使用:DELETE FROM userwhere NAME not in (select name from usermood);
NOT EXISTS使用:DELETE FROMuser where NOT EXISTS (select from usermood where `user`.`name`=usermood.`name`);
使用实例:放在etruck_gw.gis_info表
sql_dele="delete from etruck_gw.gis_info where NOTEXISTS(select * from etruck_gw.dev whereetruck_gw.gis_info.id=etruck_gw.dev.last_gis_info_id andetruck_gw.gis_info.created_time >= '${tmpdateh0}' andetruck_gw.gis_info.created_time < '${tmpdateh1}')"
放在etruck_gw.request表
sql_dele="delete from etruck_gw.request where NOTEXISTS(select * from etruck_gw.dev,etruck_gw.gis_info whereetruck_gw.request.id=etruck_gw.dev.last_req_id oretruck_gw.request.id=etruck_gw.gis_info.request_id andetruck_gw.request.updated_time >= '${tmpdateh0}' and etruck_gw.request.updated_time< '${tmpdateh1}')"
发现问题:
SELECT
count(*)
FROM
etruck_gw.gis_info,etruck_gw.request
WHERE
etruck_gw.request.id = etruck_gw.gis_info.request_id
AND etruck_gw.request.updated_time >= '2015-11-1'
AND etruck_gw.request.updated_time < '2016-12-2';
count(*)
FROM
etruck_gw.gis_info,etruck_gw.request
WHERE
etruck_gw.request.id = etruck_gw.gis_info.request_id
AND etruck_gw.request.updated_time >= '2015-11-1'
AND etruck_gw.request.updated_time < '2016-12-2';
与
SELECT
count(*)
FROM
etruck_gw.request
WHERE
EXISTS( SELECT
*
FROM
etruck_gw.gis_info
WHERE
etruck_gw.request.id = etruck_gw.gis_info.request_id)
AND etruck_gw.request.updated_time >= '2015-11-1'
AND etruck_gw.request.updated_time < '2016-12-2';
count(*)
FROM
etruck_gw.request
WHERE
EXISTS( SELECT
*
FROM
etruck_gw.gis_info
WHERE
etruck_gw.request.id = etruck_gw.gis_info.request_id)
AND etruck_gw.request.updated_time >= '2015-11-1'
AND etruck_gw.request.updated_time < '2016-12-2';
结果:97828
96757
解释:前者为等值连接,会计数重复记录
后者不记录重复记录,但做删除时结果相同。
0 0
- sql语句之IN&&EXISTS
- 比较Oracle SQL中的IN & EXISTS
- exists & in
- IN&EXISTS 与 NOT IN&NOT EXISTS
- sqlserver&&mysql分页sql语句总结
- SQL之waitfor & case
- 数据库创建(SQL语句)--DropdownList&amp;amp;ListBox连动菜单
- sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
- SQL之 exists 、in
- AMP
- AMP
- amp
- Sql语句优化之用exists、not exists替代in、not in
- PL-Sql 之 NVL & REPLACE
- C++ AMP: Staging Arrays in C++ AMP
- C++ AMP: Default accelerator in C++ AMP
- in amp 共模差模滤波
- 关于SQL语句中的双引号、单引号和&-------asp.net
- linux sort,uniq,cut,wc命令详解
- Java EE框架
- CentOS6.x下配置sendmail发邮件
- hdoj-1025===最长非降子序列(O(nlgn))
- Java EE学习博文
- sql语句之IN&&EXISTS
- 那些未知
- 发邮件之Mutt详解
- 一步一步实现一个简单的OS(简单的让boot加载setup)
- PAT (Basic Level)1026. 程序运行时间
- IDEA使用--字体、编码和基本设置
- c++ 容易出错的笔试题
- 在Mysql存储过程中使用事务实例
- 尝试使用博客