34,837
社区成员




SELECT TOP m customer1.namefull,customer2.tel
FROM customer1,customer2
where customer1.custid not in
(select TOP n customer1.custid
FROM customer1,customer2
WHERE customer2.custid=customer1.custid
ORDER BY customer1.setdate DESC )
and customer2.custid=customer1.custid
ORDER BY customer1.setdate DESC
--注:必须要有惟一字段,这里假定col1是惟一字段。
declare @tb table (
col1 varchar(3),
col2 varchar(10)
)
insert into @tb
select 'b1','aaa' union all
select 'm2','bbbb' union all
select 'y3','cccc' union all
select 'c4','dddd' union all
select 'r5','eeee' union all
select 'u6','ffff' union all
select 'h7','gggg' union all
select 'n8','hhhh' union all
select 'v9','iiii' union all
select 'y10','jjjj' union all
select 'c11','kkkk' union all
select 'a12','llll' union all
select 'z13','mmmm' union all
select 'p14','nnnn' union all
select 't15','oooo' union all
select 'm16','pppp'
select *
from @tb
order by col1
--7-12
--(12-7)+1为6,所以为: top 6 *
select top 6 *
from @tb
where col1 not in (select top 6 col1 from @tb)
/*
col1 col2
---- ----------
h7 gggg
n8 hhhh
v9 iiii
y10 jjjj
c11 kkkk
a12 llll
(所影响的行数为 6 行)
*/
--注:必须要有惟一字段,这里假定col1是惟一字段。
--当然,为了好看,我的col1列是顺序的,其实无规律也可以
declare @tb table (
col1 int,
col2 varchar(10)
)
insert into @tb
select 1,'aaa' union all
select 2,'bbbb' union all
select 3,'cccc' union all
select 4,'dddd' union all
select 5,'eeee' union all
select 6,'ffff' union all
select 7,'gggg' union all
select 8,'hhhh' union all
select 9,'iiii' union all
select 10,'jjjj' union all
select 11,'kkkk' union all
select 12,'llll' union all
select 13,'mmmm' union all
select 14,'nnnn' union all
select 15,'oooo' union all
select 16,'pppp'
--7-12
--(12-7)+1为6,所以为: top 6 *
select top 6 *
from @tb
where col1 not in (select top 6 col1 from @tb)
/*
col1 col2
----------- ----------
7 gggg
8 hhhh
9 iiii
10 jjjj
11 kkkk
12 llll
(所影响的行数为 6 行)
*/
--给你个老土的办法 用一个临时表
SELECT TOP 12 id=identity(int,1,1), customer1.namefull,customer2.tel into # FROM customer1,customer2 WHERE customer2.custid=customer1.custid ORDER BY customer1.setdate DESC
--查找第七行到十二行的数据
select top 5 namefull,tel from # order by id desc
--sql server 2005 可以用row_number函数解决.如下:
select * from
(
select *,row_number() over(order by addressid) px from person.address
) t
where px between 7 and 12
取n到m条记录的语句
1.
select top m * from tablename where id not in (select top n id from tablename)
2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果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
5.如果表里有identity属性,那么简单:
select * from tablename where identitycol between n and m
select sal from
(select row_number() over (order by sal) as rn,
sal
from emp) x
where rn between 7 and 12
SELECT TOP 12 * INTO #tb FROM customer ORDER BY indexcol DESC
--indexcol 为索引列
SELECT TOP 6 * FROM #tb
SELECT px=identity(int,1,1),* INTO #a FROM customer
SELECT * FROM #a WHERE px>5 AND px<13
DROP TABLE #a