select * from ydbdlist where convert(char(6), actdate, 112)='2005-05' and station<'Y' and sc# not in
(
select sc# from ydbdptby where convert(char(6), proddate, 112) ='2005-05'
) order by recdate---把<>號,改>號看看
(
select sc# from ydbdptby where convert(char(6), proddate, 112) ='2005-05'
) order by recdate---把<>號,改>號看看
from ydbdlist a
inner join ydbdptby b
on convert(char(6),a.actdate, 112)=convert(char(6), proddate, 112)
and a.sc#<>b.sc#
where a.station<>'Y'
and convert(char(6),a.actdate, 112)='200505'
order by a.recdate
select * from (select * from ydbdlist where convert(char(6), actdate, 112)='2005-05' and station<'Y' )Twhere not exists (select 1 from T left join ydbdptby on convert(char(6),ydbdptby. proddate, 120)='2005-05' and T.sc#=ydbptby.sc#)
where --convert(char(6), actdate, 112)='200505'
actdate>='2005-5-1' and actdate<'2006-1-1' --直接在字段上查询,不要把字段包含在函数中
and station<>'Y'
and not exists( --exists 比 not in 效率好
select * from ydbdptby
where ydbdptby.s#=ydbdlist.s#
-- and convert(char(6), proddate, 112) ='200505'
and proddate>='2005-5-1' and proddate<'2006-1-1' --直接在字段上查询,不要把字段包含在函数中
) order by recdate
在将字段包含在函数中,会导致索引失效,而索引是提高查询效率的关键,所以任何把字段放在函数中,或者对字段进行计算的处理都应该想办法改掉.