select * from t where not exists( select * from(select top 10 * from t)a where t.id=a.id)
declare @a table(b int,c int) insert into @a select 1,1 union all select 2,2 union all select 1,3 union all select 2,4 union all select 1,5 union all select 2,6 union all select 3,7 union all select 4,8 union all select 1,9 union all select 2,10 union all select 5,11 select * from @a b where not exists(select * from(select top 10 * from @a)a where b.c=a.c)
exists --不返回任何記錄, 只是一個存在測試,返回TRUE或FALSE
应该效率不如如下(但是没用not exists):declare @a table(b int,c int) insert into @a select 1,1 union all select 2,2 union all select 1,3 union all select 2,4 union all select 1,5 union all select 2,6 union all select 3,7 union all select 4,8 union all select 1,9 union all select 2,10 union all select 5,11 select * from @a b where (select count(*) from @a where c<=b.c)>10
假设没有自增列ID,可以这样: select identity(int,1,1) as id, * into # from tselect * from # where not exists( select * from(select top 10 * from #)a where #.id=a.id)
如果有id 主键 select * from t a where not exists(select top 10 id from t where id=a.id)--id主键和复合主键唯一标识用法 如果没有只能用临时表实现(楼主的方法)
我的意思和楼上的以意思基本相同: 当然你需要现设置好排序的顺序。才好来做 select * from t a where id not in(select top 10 id from t )
应该有主键吧select * from t a left outer join (select top 10 主键 from t) b on a.主键=b.主键 where b.主键 is null
微软的有关 NOT EXISTS 事例: ---------------------------------- 下面的查询查找已经不销售的书的名称。 USE pubs SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id) -------------- 这里面有两个表的titles 和 sales , 在 WHERE title_id = titles.title_id 两个表进行了关联,有一个子的查询结果中间出现的。 然后用 NOT EXISTS 利用这个中间结果排出筛选需要的结果的。感觉结构乱乱的,不清晰!
SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.id = B.id) GO
declare @a table(b int,c int) insert into @a select 1,1 union all select 2,2 union all select 1,3 union all select 2,4 union all select 1,5 union all select 2,6 union all select 3,7 union all select 4,8 union all select 1,9 union all select 2,10 union all select 5,11 select * from @a b where (select count(*) from @a where c<=b.c)>10
SELECT * FROM t WHERE id NOT IN (SELECT TOP 2 id FROM t)
insert into @a select 1,1 union all
select 2,2 union all
select 1,3 union all
select 2,4 union all
select 1,5 union all
select 2,6 union all
select 3,7 union all
select 4,8 union all
select 1,9 union all
select 2,10 union all
select 5,11
select * from @a b where not exists(select * from(select top 10 * from @a)a where b.c=a.c)
insert into @a select 1,1 union all
select 2,2 union all
select 1,3 union all
select 2,4 union all
select 1,5 union all
select 2,6 union all
select 3,7 union all
select 4,8 union all
select 1,9 union all
select 2,10 union all
select 5,11
select * from @a b
where (select count(*) from @a where c<=b.c)>10
select identity(int,1,1) as id, * into # from tselect * from #
where not exists( select * from(select top 10 * from #)a where #.id=a.id)
select * from t a
where not exists(select top 10 id from t where id=a.id)--id主键和复合主键唯一标识用法
如果没有只能用临时表实现(楼主的方法)
当然你需要现设置好排序的顺序。才好来做
select * from t a
where id not in(select top 10 id from t )
from t a left outer join
(select top 10 主键 from t) b on a.主键=b.主键
where b.主键 is null
----------------------------------
下面的查询查找已经不销售的书的名称。
USE pubs
SELECT title
FROM titles
WHERE NOT EXISTS
(SELECT title_id
FROM sales
WHERE title_id = titles.title_id)
--------------
这里面有两个表的titles 和 sales ,
在 WHERE title_id = titles.title_id 两个表进行了关联,有一个子的查询结果中间出现的。
然后用 NOT EXISTS 利用这个中间结果排出筛选需要的结果的。感觉结构乱乱的,不清晰!
FROM A WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.id = B.id)
GO
insert into @a select 1,1 union all
select 2,2 union all
select 1,3 union all
select 2,4 union all
select 1,5 union all
select 2,6 union all
select 3,7 union all
select 4,8 union all
select 1,9 union all
select 2,10 union all
select 5,11
select * from @a b
where (select count(*) from @a where c<=b.c)>10
FROM t WHERE id NOT IN
(SELECT TOP 2 id
FROM t)