select top 1 * from table1 --第1条 select top 1 * from table1 where id not in (select top 99 id from table1) --第100条
用sqlAdapter把结果集放到datatable里面然后dt.rows[99]
SQL SERVER 2005 支持ROW_NUMBER OVER函数。SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE ) T WHERE T.ROWID=1 OR T.ROWID=100 SQL SERVER 2000 的话可以用Identity(int, 1, 1)SELECT * FROM ( SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME FROM EMPLOYEE ORDER BY EMPID ) T WHERE T.ROWID=1 OR T.ROWID=100
1和100,200,300, 400 那就是1和100的整数倍咯(对100求余数=0) SELECT * FROM ( SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME FROM EMPLOYEE ORDER BY EMPID ) T WHERE T.ROWID=1 OR T.ROWID%100=0
谢谢楼上的 但是我不是这个意思啊 我的意思是说我要分别读取第1条,第100条,第200条,第300条,第400条,第500条.... 我怎不能写那么多语句吧--用sql语句写 如果有主键列id``可以这样: select * from (select px=(select count(1) from 表名 where id<a.id)+1 from 表名 a)b where px=1 or px%100=0
请问8楼 SELECT * FROM ( SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME FROM EMPLOYEE ORDER BY EMPID ) T WHERE T.ROWID=1 OR T.ROWID%100=0 中的ROWID EMPID FNAME LNAME等等都分别代表什么呢?谢谢
我改后的语句是SELECT * FROM (SELECT ROWID=IDENTITY(int,1,1),frequency FROM N0_Data ORDER BY frequency ) T WHERE T.ROWID=1 OR T.ROWID%100=0 可是提示:"关键字 'IDENTITY' 附近有语法错误。"请问这是怎么回事啊?谢谢你了
谢谢8楼的 我用SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE ) T WHERE T.ROWID=1 OR T.ROWID=100 语句成功了还有一个问题就是我能不能同时得到数据库记录总数呢?(不用COUNT(*))?
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, *, count(*) AS CNT FROM EMPLOYEE ) T WHERE T.ROWID=1 OR T.ROWID=100 让每条数据都带上Count,就可以了。
我这样的代码显示有错啊SELECT frequency FROM ( SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, * ,count(*) AS CNT FROM N2_Data ) T WHERE T.ROWID=1 OR T.ROWID%10000=0这是怎么回事,不加",count(*) AS CNT "是对的,加了就有问题了
Sql server 2005: SELECT a.num, table1.* FROM table1, (select id, ROW_NUMBER() over (ORDER BY id) as num from table1) a WHERE (a.num % 100 =0 or a.num = 1) and a.id = table1.idPS: ID为你要进行排序的字段 返回1,100,200,300,400,......10000,......
用sql语句也可以,用程序控制也可以
程序控制是不是指用sqldatareader啊 那样我觉得速度不怎么样啊?
那么你就当独取一下Count呗。 或者这样:SELECT frequency, ( select count(*) from n2_data ) as cnt FROM ( SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, * FROM N2_Data ) T WHERE T.ROWID=1 OR T.ROWID%10000=0
谢谢fangxinggood功能是完成了,但是运行的速度好慢啊? 能不能改进一下加快速度啊,谢谢
改进方案: 1. select count(*) from n2_data 单独拿出来查询一次。(这个是影响速度的关键) 2. 用WITH关键字,另外不用*了,因为你只要N2_Data中的frequency字段。
WITH T As ( SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, frequency FROM N2_Data ) SELECT frequency FROM T
select count(*) from n2_data这个查询语句也很耗时啊,能不能改进啊?
WITH T As (SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, frequency FROM N2_Data) SELECT frequency FROM T WHERE T.ROWID=1 OR T.ROWID%10000=0提示:"关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。"怎么回事啊?麻烦指正!
WITH T AS (SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, frequency FROM N2_Data) SELECT frequency FROM T WHERE T.ROWID=1 OR T.ROWID%10000=0我没有测试环境哦。这个如果不行,你还用子查询方式吧。select count(*) from n2_data 这个如果很慢,那你就应该查查看你这个表有没有建立索引了。
用这个也行了,只要把Count提出去,这个效率也不是很差的。SELECT frequency FROM ( SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, * FROM N2_Data ) T WHERE T.ROWID=1 OR T.ROWID%10000=0
select top 1 * from table1 where id not in (select top 99 id from table1) --第100条
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, *
FROM EMPLOYEE
) T
WHERE T.ROWID=1 OR T.ROWID=100
SQL SERVER 2000 的话可以用Identity(int, 1, 1)SELECT *
FROM (
SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME
FROM EMPLOYEE ORDER BY EMPID
) T
WHERE T.ROWID=1 OR T.ROWID=100
那就是1和100的整数倍咯(对100求余数=0)
SELECT *
FROM (
SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME
FROM EMPLOYEE ORDER BY EMPID
) T
WHERE T.ROWID=1 OR T.ROWID%100=0
但是我不是这个意思啊 我的意思是说我要分别读取第1条,第100条,第200条,第300条,第400条,第500条.... 我怎不能写那么多语句吧--用sql语句写
如果有主键列id``可以这样:
select * from
(select px=(select count(1) from 表名 where id<a.id)+1 from 表名 a)b
where px=1 or px%100=0
SELECT *
FROM (
SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME
FROM EMPLOYEE ORDER BY EMPID
) T
WHERE T.ROWID=1 OR T.ROWID%100=0
中的ROWID EMPID FNAME LNAME等等都分别代表什么呢?谢谢
我用SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, *
FROM EMPLOYEE
) T
WHERE T.ROWID=1 OR T.ROWID=100 语句成功了还有一个问题就是我能不能同时得到数据库记录总数呢?(不用COUNT(*))?
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, *, count(*) AS CNT
FROM EMPLOYEE
) T
WHERE T.ROWID=1 OR T.ROWID=100
让每条数据都带上Count,就可以了。
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, * ,count(*) AS CNT
FROM N2_Data
) T
WHERE T.ROWID=1 OR T.ROWID%10000=0这是怎么回事,不加",count(*) AS CNT "是对的,加了就有问题了
SELECT
a.num,
table1.*
FROM
table1,
(select id, ROW_NUMBER() over (ORDER BY id) as num from table1) a
WHERE
(a.num % 100 =0 or a.num = 1)
and
a.id = table1.idPS: ID为你要进行排序的字段
返回1,100,200,300,400,......10000,......
那样我觉得速度不怎么样啊?
或者这样:SELECT frequency,
(
select count(*) from n2_data
) as cnt
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, *
FROM N2_Data
) T
WHERE T.ROWID=1 OR T.ROWID%10000=0
能不能改进一下加快速度啊,谢谢
1. select count(*) from n2_data 单独拿出来查询一次。(这个是影响速度的关键)
2. 用WITH关键字,另外不用*了,因为你只要N2_Data中的frequency字段。
WITH T As (
SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID,
frequency
FROM N2_Data
)
SELECT frequency
FROM T
frequency
FROM N2_Data)
SELECT frequency FROM T WHERE T.ROWID=1 OR T.ROWID%10000=0提示:"关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。"怎么回事啊?麻烦指正!
(SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, frequency FROM N2_Data)
SELECT frequency FROM T WHERE T.ROWID=1 OR T.ROWID%10000=0我没有测试环境哦。这个如果不行,你还用子查询方式吧。select count(*) from n2_data 这个如果很慢,那你就应该查查看你这个表有没有建立索引了。
能不能修改一下啊
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY abstime ASC) AS ROWID, *
FROM N2_Data
) T
WHERE T.ROWID=1 OR T.ROWID%10000=0
要一分钟
共320000条记录