用网上抄的小例子
exists的执行 顺序是外查询的一条记录带入exists子查询里,如果exists子查询有记录返回,那这条记录就会保留,如果用的是not exists,那这条记录不会保留.
优化实例
compete 表和product 表关联,compete inner join product on compete.competitorId = product.productId ,一条compete.competitorId 可能对应多条product.productId,要求只和product.id最大的那条对应
用 in 的写法
select distinct compete.competitorName, DATE_FORMAT(product.setupDate, '%Y-%m-%d') setupDate ,product.industryName, product.businessScope,product.compName from compete inner join product on compete.competitorId = product.productId where compete.compcode=2310776993 and compete.datastatus !=3 and product.id in (select max(id) from product group by compCode ) order by setupDate desc
用 exists
select distinct compete.competitorName, DATE_FORMAT(product.setupDate, '%Y-%m-%d') setupDate ,product.industryName, product.businessScope,product.compName from sy_cd_me_buss_comp_compete compete inner join sy_cd_ms_base_comp_product product on compete.competitorId = product.productId where compete.compcode=2310776993 and compete.datastatus !=3 and not EXISTS (select 1 from product2 where product.id < product2.id and product.compCode=product2.compCode) order by setupDate desc
product 和product2是同一张表的别名
用 in 要把 product表全部数据按compcode分组,用exists是把外查询的数据带到exists里
