select top 20 * from news where (id not in(select top 10 * from news)) order by id asc我这么写的 但是有错 消息 116,级别 16,状态 1,第 1 行 当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。要怎么写啊?
N-M条记录 1. select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入 set rowcount n select * from 表变量 order by columnname desc 2. select top n * from (select top m * from tablename order by columnname) a order by columnname desc 3. 如果tablename里没有其他identity列,那么: select identity(int) id0,* into #temp from tablename取n到m条的语句为: select * from #temp where id0 >=n and id0 <= m如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行: exec sp_dboption 你的DB名字,'select into/bulkcopy',true 4. 如果表里有identity属性,那么简单: select * from tablename where identitycol between n and m
select *,ROW_NUMBER() over(order by id ) as cnt from news where cnt between 1 and 20
select top 10 * from (select top 20 * from news order by id) tb1 order by id descselect * from (select top 10 * from (select top 20 * from news order by id) tb1 order by id desc) order by id
select * from (select top 10 * from (select top 20 * from news order by id) tb1 order by id desc) tb2 order by id
select * from ( select top 10 * from( select top 20 * from news order by ID )a order by ID desc )b order by id
用子查询,先查询前20条,再查询10条不就行了。 select top 10 * from (select top 20 * from 表) t order by id desc
select top 20 * from news where id not in(select top 10 [id] from news) order by [id] asc 去掉外围括号 把条件语句里的 * 号 改成 id试一下
试试这个 select a.* from (select ROW_NUMBER() OVER(ORDER BY id) as ID,Item_Number FROM BItemMaster) as a where a.ID between 11 and 20
如果是 sql 2005 可以用 楼上的方法。
select top 10 * from news where id not in(select top 10 [id] from news )
select * from 表名 where id(标识列) between 10 and 20 order by id(标识列)
查出top10中最大的值 和 top20中最大的值...between一下就行了
SELECT * from (SELECT TOP 20* FROM news )a WHERE a.ID NOT IN(SELECT TOP 10 ID FROM news ) order by ID asc select top 20 * from news where (id not in(select top 10 * from news)) order by id asc 这样就好了。。你先把前20 条搞成临时表就好了。。再慢慢筛选。。你这句不行的,条件中括号的ID根本不知道哪里来。。即使括号去掉。。先后顺序也错了。。这样,只是先去掉头10条,再取下面的20条记录。。
10到20,如果含第10的话,可是11个记录哟,select top 11 * from news where id not in (select top 9 id from news ) order by id
select top 11 * from (select top 20* from news order by id asc) B order by B.id desc
select top 10 * from (select top 20 * from news order by id asc) b order by id desc
select top 11 * from news where id not in (select top 9 id from news )--注意,你的10到20条数据的查询结果其实是11条结果!所以这里是top 11和9
select top 4 * from (select * from score ) as a order by score descif exists(select * from sysobjects where name='temp') drop table temp select identity(int,1,1) as id0,score into temp from score select * from temp where id0>=4 and id0<=7select * from score where identitycol between 4 and 7
select top 4 * from (select * from score ) as a order by score descif exists(select * from sysobjects where name='temp') drop table temp select identity(int,1,1) as id0,score into temp from score select * from temp where id0>=4 and id0<=7select * from score where identitycol between 4 and 7
select top 10 * from news where (id not in(select top 10 id from news)) order by id asc
1.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
2.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
3.
如果tablename里没有其他identity列,那么:
select identity(int) id0,* into #temp from tablename取n到m条的语句为:
select * from #temp where id0 >=n and id0 <= m如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
4.
如果表里有identity属性,那么简单:
select * from tablename where identitycol between n and m
from news
where cnt between 1 and 20
select top 10 * from (select top 20 * from news order by id) tb1 order by id descselect * from (select top 10 * from (select top 20 * from news order by id) tb1 order by id desc) order by id
(
select top 10 *
from(
select top 20 *
from news
order by ID
)a
order by ID desc
)b
order by id
select top 10 * from (select top 20 * from 表) t order by id desc
我就是这么写的啊?
但是,出错了
没有用 EXISTS 引入子查询
你说怎么办?
select top 20 *
from news
where
id not in(select top 10 [id] from news) order by [id] asc
去掉外围括号
把条件语句里的 * 号 改成 id试一下
select a.* from
(select ROW_NUMBER() OVER(ORDER BY id) as ID,Item_Number FROM BItemMaster) as a
where a.ID between 11 and 20
from news
where
id not in(select top 10 [id] from news )
SELECT * from (SELECT TOP 20* FROM news )a WHERE a.ID NOT IN(SELECT TOP 10 ID FROM news ) order by ID asc
select top 20 * from news where (id not in(select top 10 * from news)) order by id asc
这样就好了。。你先把前20 条搞成临时表就好了。。再慢慢筛选。。你这句不行的,条件中括号的ID根本不知道哪里来。。即使括号去掉。。先后顺序也错了。。这样,只是先去掉头10条,再取下面的20条记录。。
select top 11 * from (select top 20* from news order by id asc) B order by B.id desc
select top 11 * from news where id not in
(select top 9 id from news )--注意,你的10到20条数据的查询结果其实是11条结果!所以这里是top 11和9
(select * from score ) as a
order by score descif exists(select * from sysobjects where name='temp')
drop table temp
select identity(int,1,1) as id0,score into temp from score select * from temp where id0>=4 and id0<=7select * from score where identitycol between 4 and 7
(select * from score ) as a
order by score descif exists(select * from sysobjects where name='temp')
drop table temp
select identity(int,1,1) as id0,score into temp from score select * from temp where id0>=4 and id0<=7select * from score where identitycol between 4 and 7