麻烦各位大侠在新年帮小弟解答一下。
祝各位新年愉快。问题如下有一个Staff 表 一个Organization表
SELECT *
FROM Staff a where a.OrganizationID in (40,60,45,51,1,3,4,5,6,7,7,8,9,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59) 请问如上SQL语句,如何改造,使得他的执行效率更高呢?
网上说用in 效率低,用exist 和left join代替该如何写好呢?
祝各位新年愉快。问题如下有一个Staff 表 一个Organization表
SELECT *
FROM Staff a where a.OrganizationID in (40,60,45,51,1,3,4,5,6,7,7,8,9,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59) 请问如上SQL语句,如何改造,使得他的执行效率更高呢?
网上说用in 效率低,用exist 和left join代替该如何写好呢?
a.OrganizationID 没有索引的话建一个索引吧。
SELECT *
FROM Staff a where (a.OrganizationID between 1 and 60) and a.OrganizationID <> 2
a.OrganizationID <> 10 and a.OrganizationID <> 11--看到LZ的a.OrganizationID除了2、10、11外,其他都有,可以这么试试!
where a.OrganizationID not exist
(select * from staff a where a.OrganizationID<>2 and a.OrganizationID<>10
and a.OrganizationID<>11)
--1SELECT *
FROM Staff a left join Organization b on a.OrganizationID = b.OrganizationID--2SELECT *
FROM Staff a
WHERE a.OrganizationID exists (select 1 from Organization b where a.OrganizationID = b.OrganizationID)
FROM Staff a
WHERE a.OrganizationID exists (select 1 from Organization b where a.OrganizationID = OrganizationID第二个多了个 b.
FROM Staff where OrganizationID =1 or (OrganizationID>2 and OrganizationID<10) or(OrganizationID>11 and OrganizationID<61)
SELECT *
FROM Staff a left join Organization b on a.OrganizationID = b.OrganizationID or b.OrganizationID =1 or b.OrganizationID =2?
这样吗 ?
在数据库里面执行全部staff的数据都出来了, 不仅仅是OrganizationID =1 或者2的
糊涂了,LZ,你如果是要找固定值的话!为什么还要在Organization表里找呢?要不带点数据说下吧!
然后
select a.*
FROM Staff a join b on a.OrganizationID=b.col