SQL语句优化举例

同样一个目的，可以用不同的SQL语句来实现，但效率上会有不同，甚至同样的语句，在不同的SQL Server版本中，效率也可能不一样。所以，使用更优化的语句来操作数据库，是有必要的。这里就来举一些例子。

取代 not in 的查询与删除语句：

select aa.* from aa left join bb on aa.id=bb.id where bb.id is null

select aa.* from aa where Not Exists (select * from bb where bb.F1=aa.F1)

delete from aa where not exists(select * from bb where aa.myID=bb.myID)

结构相同的两个表之间复制记录，若有IDENTITY栏位，先关掉标识规范再复制再恢复IDENTITY，可事半功倍：

insert into msEvenLog select * from msEvenOld

删除重复的记录：

delete from aa where id not in
     (select max(id) from aa group by col1,col2,col3...)  

取最大值的记录 — 报价排行榜

select * from poItPrice aa where aa.price=
  (select min(price) from poItPrice bb where bb.itID=aa.itID)

从工艺表(BOM结构表)中取各产品的“PJ”工序的前一道工序，ViewOrder表示第几道工序

select dA.itID,dA.LineNum from prRoute dA,
(select d1.itID,max(d1.ViewOrder)as PreOrder from prRoute d1,prRoute d2 
  where d1.itID=d2.itID and d1.ViewOrder<d2.ViewOrder and d2.LineNum='PJ'
  group by d1.itID)as dB 
where dA.itID=dB.itID and dA.ViewOrder=dB.PreOrder

人力资源管理中的【用餐统计】，本例中hraBrush是刷卡表，hreEmployee是员工基础资料表，hreDept是部门表

select d3.dpna,d1.WkNo,d1.WkNa,d2.Date0,
  (select top 1 convert(varchar(8),d5.OnTime,108) from jhERPsu..hraBrush d5 
      where d5.EmplID=d1.EmplID and Convert(Char(10),d5.OnTime,120)=d2.Date0 
      and convert(varchar(8),d5.OnTime,108)>='11:30:00'
      and convert(varchar(8),d5.OnTime,108)<='12:30:59')as '中餐',
  (select top 1 convert(varchar(8),d5.OnTime,108) from jhERPsu..hraBrush d5 
      where d5.EmplID=d1.EmplID and Convert(Char(10),d5.OnTime,120)=d2.Date0 
      and convert(varchar(8),d5.OnTime,108)>='16:45:00'
      and convert(varchar(8),d5.OnTime,108)<='17:50:59')as '晚餐'
from jhERPsu..hreEmployee d1 ,
  jhERPsu..hreDept d3,
  (select distinct EmplID,Convert(char(10),OnTime,120) as Date0 from jhERPsu..hraBrush d1
    where d1.OnTime>=@param1 and d1.OnTime<@param2) d2
where d1.EmplID=d2.EmplID
and d1.Dept=d3.Dept

找出小数位数第五位有值的记录的过滤代码

where cast(Price*10000 as int)<Price*10000

where convert(decimal(18,5),PostedQty) < PostedQty

where ceiling(qty*100000)<>qty*100000

批量更新合计值的两种方法

----有交易明细的物项才更新合计值
Update d1 set d1.OrderedQty=d2.poQty from stItSum d1,
  (select itID,HouseID,sum(qtyIng)as poQty from mpReqLine 
    where mpReqType in('PI','WI') group by itID,HouseID) d2
  where d1.itID=d2.itID and d1.HouseID=d2.HouseID

----所有记录都参与更新，没有明细的更新为Null
Update mpReqTable set VendID=(select top 1 d2.VendID from poItPrice d2,vacExchRate d3
  where d1.itID=d2.itID and d2.Currency=d3.Currency order by d2.Price*d3.ExchRate)
  from mpReqTable d1 

求百分比，须考虑分母可能是0或空的情况

select IqcId,ckErr1,AqlQty,ckItem,
round(isNull(ckErr1,0)*100.0/(case isNull(AqlQty,0) when 0 then 1 else AqlQty end),0) as ckPercent
 from w_IqcInsCK 

在查询语句中启用raise功能，if @Money > @Stand 是判断条件

begin tran

if @Money > @Stand
begin
 rollback tran
 raiserror('客户超出信用额度,不充许批准',-1,-1,'')
 return
end

update soSaleTable set  soStatus='2',ConfirBy='abc', ConfirDate=convert(varchar(10),getdate(),121)
 where SaleID='SO11000156'

commit tran

查询时指定索引。请注意，主从表查询时从表不能用 with(Index=…)关键词，如果查询结果用于只读，加上关键词with(NOLOCK)会快一些。查询表中有哪些索引：EXEC sp_helpindex ‘ProdJournalBom’。

select * from ProdJournalBom with(INDEX=I_239LINEIDX) where DataAreaID='fin'

select * from ProdJournalBom with(NOLOCK) where DataAreaID='fin'

取代某栏位中的某些字符

update ccVoucher set itDesc=replace(itDesc,'村府','ConairCCL') where itDesc like '%村府%'

用 CharIndex 代替 like，效果更好

select d1.Number,d2.ChineseName,d1.MobileNum,d1.Destination,d3.DangerArea 
  from jh_chun2021 d1
  left join Employees d2 on d1.Number=d2.Number
  left join jh_danger d3 on CharIndex('深圳',d1.Destination)>0

在A表中插入相比B表欠缺的记录

Insert into MouldGoods(MouldNo,ItemID,DataAreaID,ModifiedDate)
  Select MouldNo,ItemID,'v01',getDate() from '+tmpDB2+' d1 where not exists
    (select * from MouldGoods where MouldNo=d1.MouldNo and ItemID=d1.ItemID )

Insert into mdQcPlsTable(sono,TypeNo,bLock)
 Select  d1.SoNo,d1.ItemType,0 
 from ieSoList d1 left join mdQcPlsTable d2  on d1.sono=d2.SoNo 
  where d2.sono is null

查询表中是否有重复的脸谱，注：template是保存脸谱数据的Image类型栏位，EnrollNumber是保存员工ID的int类型栏位。

select EnrollNumber,count(*) from 
 (select EnrollNumber,CAST([template] as varbinary(8000)) as FaceId from u500_tblzEnroll) dA
 group by EnrollNumber,FaceId having count(*)>1

从千万级数据表中查询数据，三种 in 的效率对比

select top 38 SPL_POCode+ltrim(PurchId) as PurchId
from PurchTable where (ltrim(PurchId)=@param1 or ltrim(OrderAccount)=@param1 
or PurchId+DataAreaId in (select distinct PurchId+DataAreaId from PurchLine where ItemID like '%'+@param1+'%'))
----耗时 6 秒

select top 38 SPL_POCode+ltrim(PurchId) as PurchId
from PurchTable d1 where (ltrim(PurchId)=@param1 or ltrim(OrderAccount)=@param1 
or exists (select * from PurchLine d2 where d1.PurchId=d2.PurchId
and d1.DataAreaId=d2.DataAreaId and d2.ItemId like '%'+@param1+'%'))
----耗时 1 秒

select top 38 d1.SPL_POCode+ltrim(d1.PurchId) as PurchId
from PurchTable d1,PurchLine d2
where d1.PurchId=d2.PurchId and d1.DataAreaId=d2.DataAreaId
and (ltrim(d1.PurchId)=@param1 or ltrim(d1.OrderAccount)=@param1 
or d2.ItemId like '%'+@param1+'%')
----耗时 1 秒

其它一些建议：

1. 实测显示，64位的SqlServer2008用上Order By后速度慢很多，应该在前端的数据控件里用 Sort:=” 来排序。
2. 数据类型 decimal 和 numeric 在小数存储方面一样，M$建议使用Decimal。
3. 自动增长标识(IDENTITY)的栏位，在设计期要考虑记录数封顶值，以确定用 int 还是 bigint类型。
4. 尽量用 UNION ALL 替换 UNION，避免使用OR运算符，改用Union All，不要在 like 条件值的前面加通配符%。
5. IN子句中条件个数要小于100个，预估超过的话，改用其它方案。