Access中notin优化⽅式
Access中使⽤not in效率低下，可以使⽤外连接语句代替：
代码
--改造前写法
SELECT TOP50 StockholderID,HaveRate,HaveNum
FROM (
SELECT StockholderID,HaveRate,HaveNum
FROM StockInfo
WHERE
CapitalStockID =104
AND StockholderID NOT IN (
SELECT StockholderID FROM StockInfo WHERE
CapitalStockID =105)
ORDER BY HaveRate DESC
) AS A
--改造后写法
SELECT TOP50 StockholderID,HaveRate,HaveNum
FROM (
SELECT a.StockholderID,a.HaveRate,a.HaveNum FROM StockInfo a left outer join (
SELECT c.StockholderID,c.HaveRate,c.HaveNum FROM StockInfo c WHERE c.CapitalStockID =105
) as b
on a.StockholderID = b.StockholderID
WHERE CapitalStockID =104and b.StockholderID is null
ORDER BY a.HaveNum DESC
) as t
--例⼦1
select TOP50 a.Name,a.StockholderID,-5from CollectNew a left outer join (
SELECT TOP50 c.Name,c.StockholderID FROM CollectOld c ORDER BY OldNum DESC
) as b
on a.StockholderID=b.StockholderID
where b.StockholderID is null
ORDER BY a.NewNum DESC
-
-排序后会按照a表获取结果，如果想要差异性内容，不能加排序，或者在外层嵌套⼀层查询Select * from（）ORDER ....
--例⼦⼆
select a.*from generator a left outer join (select c.busno,c.id from generator c,ta_generator d where c.busno= d.busno) as b
join和in哪个查询更快on a.busno=b.busno
where b.busno is null

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。