帮小弟我优化一句sql去除NOT IN语句

帮我优化一句sql去除NOT IN语句

SQL code

EXEC('SELECT TOP '+@count+' * FROM [webcontent] WHERE enable=1 AND ID NOT IN(SELECT TOP '+@index+' ID FROM [webcontent] WHERE enable=1 ORDER BY [we_time] DESC) ORDER BY [we_time] DESC');

语句如上，可以正常使用，可是NOT IN对数据查询不好，请高手帮我去除，代换，提高查询速度。

用以上的NOT IN查询 82万条数据时就变得很慢了。
谢谢！

[解决办法]
不知道你是需要升序,还是倒序?

SQL code

exec('SELECT TOP ' + cast(@count - @index as varchar) + ' ID FROM [webcontent] WHERE enable=1 ORDER BY [we_time] desc)'exec('SELECT TOP ' + cast(@count - @index as varchar) + ' ID FROM [webcontent] WHERE enable=1 ORDER BY [we_time])'exec('SELECT TOP ' + cast(@count - @index + 1 as varchar) + ' ID FROM [webcontent] WHERE enable=1 ORDER BY [we_time] desc)'exec('SELECT TOP ' + cast(@count - @index + 1 as varchar) + ' ID FROM [webcontent] WHERE enable=1 ORDER BY [we_time])'
[解决办法]
SQL code
--try:EXEC('select * from (SELECT TOP '+ltrim(@count)+' * FROM (select top '+ltrim(@index+@count)+' * from webcontent where enable=1 order by we_time desc)a order by we_time)a order by we_time desc')另外给id,we_time字段加上索引。。
[解决办法]
SQL code
EXEC('select   *  from (   SELECT        TOP '+ltrim(@count)+' *    FROM       (select           top '+ltrim(@index+@count)+' *        from          webcontent        where          enable=1         order by           we_time desc)a  order by we_time)a order by we_time desc')
[解决办法]
看你想要表达的意思应该是：查找一个记录集第M条到第N条记录。
你可以用你的语句结合，Exception关键字来实现
SELECT TOP M FROM TABLE WHERE .... ORDER BY ....
EXCEPTION
SELECT TOP N FROM TABLE WHERE .... ORDER BY .... 

 

查看更多 下一篇