高手帮忙优化下SQL语句

Question

select top 20 Projects.id,Projects.Project_PayMin,Projects.Project_PayMax,
Projects.Project_SelectBiddingPay,Projects.Project_Name,Projects.Project_EndTime,
Projects.Project_State ,Projects.Project_Class 
from Projects inner join Project_Type on Projects.Project_Type=Project_Type.id
 where Project_State<>0   and Project_State<>5   
and Project_State<>6   and (Project_Pay_isTrust=1 or WKSiteid<>11) 
  and Project_TypeFatherId=1   and Projects.id 
not in (select top 0 Projects.id from  Projects where Project_State<>0  
 and Project_State<>5   and Project_State<>6   and (Project_Pay_isTrust=1 or WKSiteid<>11)  
 and Project_TypeFatherId=1   order by Projects.Project_BeginTime desc) 
order by Projects.Project_BeginTime desc就这个SQL 语句 数据有点多  执行有点慢

Answer 1

select top 20 Projects.id,Projects.Project_PayMin,Projects.Project_PayMax,
Projects.Project_SelectBiddingPay,Projects.Project_Name,Projects.Project_EndTime,
Projects.Project_State ,Projects.Project_Class  
from Projects inner join Project_Type on Projects.Project_Type=Project_Type.id
 where Project_State<>0 and Project_State<>5   
and Project_State<>6 and (Project_Pay_isTrust=1 or WKSiteid<>11)  
  and Project_TypeFatherId=1 and Projects.id  
not in (select top 0 Projects.id from Projects where Project_State<>0   
 and Project_State<>5 and Project_State<>6 and (Project_Pay_isTrust=1 or WKSiteid<>11)   
 and Project_TypeFatherId=1 order by Projects.Project_BeginTime desc)  
order by Projects.Project_BeginTime desc如果是 top 0 ，那么这整句都没用，可以省

Answer 2

select top 20 Projects.id,Projects.Project_PayMin,Projects.Project_PayMax,
Projects.Project_SelectBiddingPay,Projects.Project_Name,Projects.Project_EndTime,
Projects.Project_State ,Projects.Project_Class 
from Projects 
inner join Project_Type on Projects.Project_Type=Project_Type.id
 where Project_State NOT IN (0,5,6) and (Project_Pay_isTrust=1 or WKSiteid<>11) 
  and Project_TypeFatherId=1  
  order by Projects.Project_BeginTime desc
你那个NOT IN TOP 0给你删掉了

Answer 3

wo 怀疑兰州写错东西
应该是写select top 10 吧不然后面写这么多判断干吗

Answer 4

select * from
(select row=row_number() over(order by Projects.Project_BeginTime desc),Projects.id,Projects.Project_PayMin,Projects.Project_PayMax,
Projects.Project_SelectBiddingPay,Projects.Project_Name,Projects.Project_EndTime,
Projects.Project_State ,Projects.Project_Class  
from Projects inner join Project_Type on Projects.Project_Type=Project_Type.id
 where Project_State<>0 and Project_State<>5   
and Project_State<>6 and (Project_Pay_isTrust=1 or WKSiteid<>11)  
  and Project_TypeFatherId=1)a
where row between 1 and 20

Answer 5

top 0估计是写错吧not in 改为not exists 并对比较的字段建索引