not In
- SQL code
select * from table where id not in (select top 100 id from table order by id desc)
left outer join
- SQL code
select * from table left outer join (select top 100 id as tId from table order by id desc) t on t.tId = table.id where t.tId is null
两句的效果是一样的，效率上来说哪个比较好..手上没有大数据库进行测试，有没人帮忙看看
------解决方案--------------------
- SQL code
goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl(id int identity(1,1),QQ VARCHAR(10))godeclare @a intset @a=1while @a<=1000000begininsert tblselect ltrim(cast(RAND()*1000000000 as int))set @[email protected]+1endselect * from tbl where id not in (select top 100 id from tbl order by id desc)--110万条数据不加任何索引第一次30s，第二次24sselect * from tbl left join (select top 100 id as tId from tbl order by id desc) t on t.tId = tbl.id where t.tId is null--110万条数据不加任何索引第一次24s，第二次24s