把not in改为not exists
select top 20 id,mingzi from [my_user] where id not in (SELECT top 40 id FROM [my_user] order by cdate desc) order by cdate desc

麻烦高手改成效率更高的语句！听说not exists要走索引的！谢谢！ select not?in
[解决办法]

引用:

Quote: 引用:

select top 20 id,mingzi 
from [my_user] a 
left join (SELECT top 40 id FROM [my_user]  order by cdate desc) b on a.id=b.id
where a.id is null 
order by cdate desc

写成这样可能效率高一点

服务器: 消息 209，级别 16，状态 1
列名 'id' 不明确。
列名 'id' 不明确

select top 20 a.id,a.mingzi 
from [my_user] a 
left join (SELECT top 40 id FROM [my_user]  order by cdate desc) b on a.id=b.id
where a.id is null 
order by cdate desc

[解决办法]

引用:

Quote: 引用:

SELECT TOP 20
id , mingzi
FROM [my_user]
WHERE NOT EXISTS ( SELECT 1
FROM (
SELECT TOP 40
id
FROM [my_user]
ORDER BY cdate DESC
) t
WHERE id = t.id )
ORDER BY cdate DESC


不过感觉效率不会提高

你这个获得的记录为空！好像没对哦！

疏忽了一下，别名问题。

SELECT TOP 20
id , mingzi
FROM [my_user] as a

WHERE NOT EXISTS ( SELECT 1
FROM (
SELECT TOP 40
id
FROM [my_user]
ORDER BY cdate DESC
) t
WHERE a.id = t.id )
ORDER BY cdate DESC