Table : Id State
1 0
2 1
8 0
16 1
25 1
56 1
... .....10000 0要求
A. 统计 1-15 条记录 state = 1的记录条数,16-100条记录 state = 1的记录条数.....
9000-10000条记录 state = 1的记录条数 B. 查询 中间50%的记录 及 ID 降充排列,前20%不要,最后30%不要
1 0
2 1
8 0
16 1
25 1
56 1
... .....10000 0要求
A. 统计 1-15 条记录 state = 1的记录条数,16-100条记录 state = 1的记录条数.....
9000-10000条记录 state = 1的记录条数 B. 查询 中间50%的记录 及 ID 降充排列,前20%不要,最后30%不要
9000-10000条记录 state = 1的记录条数 select sum(case when id between 1 and 15 then 1 else 0 end) [1-15],
sum(case when id between 16 and 100 then 1 else 0 end) [16-100],
...自己补全
sum(case when id between 9000 and 10000 then 1 else 0 end) [9000-1000]
from table where state = 1
(
select id from (select top 20 percent id from table order by id) t
union
select id from (select top 30 percent id from table order by id desc) t
)
select sum(case when id (between 1 and 15) and state=1 then 1 else 0 end) as [1-15],
sum(case when id (between 16 and 100) and state=1 then 1 else 0 end) as [16-00]
......
from(select row_number()over(order by getdate()) as id,* from tbl)a
取n到m行1.
select top (n-m+1) * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取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 identity_col between n and m 6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m
如:
最后第一条ID = 1000000
最后第二条 ID = 50000
2005可以用row_number()over()去解决的,楼上我的方法就是