将前10条记录插入@tmp1后再执行下面的: select a.*into #tmp2 from(select a1 from table where a1 not in(select a1 from #tmp1) a ) table1 order by b1
更正: select a.*into #tmp2 from(select a1 from table where a1 not in(select a1 from #tmp1) tname ) tname order by b1
select * from emp where id not in(select top 10 id from emp)
select * into #emp2 from table1 where a1 not in(select top 10 a1 from table1)
select * into #tmp2 from table1 where a1 not in(select top 10 a.a1 from table1 a order by b1)select * from #tmp2 drop table #tmp2
好象可以用exits改in, 这句如何改?
-- NOT EXISTS Create table table1(f_id int)Insert into table1 values(1) Insert into table1 values(2) Insert into table1 values(3) Insert into table1 values(4) Insert into table1 values(5) Insert into table1 values(6) Insert into table1 values(7) Insert into table1 values(8) Insert into table1 values(9) Insert into table1 values(10) Insert into table1 values(11) Insert into table1 values(12) SELECT a.*into #tmp2 From (SELECT Table1.* FROM Table1 WHERE NOT EXISTS( SELECT Top 1 1 FROM ( SELECT Top 10 f_id from table1) as T_Temp WHERE T_Temp.f_id = Table1.f_id ) ) as ASelect * from #tmp2Drop table #tmp2 Drop table table1
将前10条记录插入@tmp1后再执行下面的:
select a.*into #tmp2 from(select a1 from table where a1 not in(select a1 from #tmp1) a ) table1 order by b1
select a.*into #tmp2 from(select a1 from table where a1 not in(select a1 from #tmp1) tname ) tname order by b1
where id not in(select top 10 id from emp)
where a1 not in(select top 10 a1 from table1)
into #tmp2
from table1
where a1 not in(select top 10 a.a1 from table1 a order by b1)select * from #tmp2
drop table #tmp2
这句如何改?
Create table table1(f_id int)Insert into table1 values(1)
Insert into table1 values(2)
Insert into table1 values(3)
Insert into table1 values(4)
Insert into table1 values(5)
Insert into table1 values(6)
Insert into table1 values(7)
Insert into table1 values(8)
Insert into table1 values(9)
Insert into table1 values(10)
Insert into table1 values(11)
Insert into table1 values(12)
SELECT a.*into #tmp2
From (SELECT Table1.*
FROM Table1
WHERE NOT EXISTS(
SELECT Top 1 1
FROM ( SELECT Top 10 f_id from table1) as T_Temp
WHERE T_Temp.f_id = Table1.f_id )
) as ASelect * from #tmp2Drop table #tmp2
Drop table table1