2005可以:select rid=row_number() over (order by getdate()),* from ta 2000不select inot没办法 ,要不你有一个自增的表导入进去
declare @table table (ID int) insert into @table select 2516 union all select 6644 union all select 2577 union all select 87select ROW_NUMBER() OVER(ORDER BY ID) AS rownum ,* from @table/* rownum ID -------------------- ----------- 1 87 2 2516 3 2577 4 6644 */
第二种方法是借助临时表declare @table table (ID int) insert into @table select 2516 union all select 6644 union all select 2577 union all select 87create table #t (rownum int identity(1,1),id int)INSERT INTO #t(id) select * from @table select * from #t drop table #t/* rownum id ----------- ----------- 1 2516 2 6644 3 2577 4 87 */
select row_ID = row_number() over (order by getdate()), * from tac_caseitem
2005环境下,确实可以。我也试过了。
declare @table table (ID int) insert into @table select 2516 union all select 6644 union all select 2577 union all select 87 SELECT *,(SELECT COUNT(*) FROM @TABLE WHERE ID<=T.ID) AS NUM FROM @TABLE T ORDER BY 2(所影响的行数为 4 行)ID NUM ----------- ----------- 87 1 2516 2 2577 3 6644 4(所影响的行数为 4 行)
ACCESS 中有函数显示行数 DCount不对这个是要排序的。好像和你的不太相符。
/* 方法一*/SELECT序号= (SELECT COUNT(客户编号) FROM 客户 AS LiMing WHERE LiMing.客户编号<= Chang.客户编号), 客户编号,公司名称 FROM客户 AS Chang ORDER BY 1; GO/* 方法二: 使用SQL Server 2005 独有的RANK() OVER () 语法*/ SELECT RANK() OVER (ORDER BY 客户编号 DESC) AS 序号, 客户编号,公司名称 FROM客户; GO/* 方法三*/ SELECT序号= COUNT(*), LiMing.客户编号, LiMing.公司名称 FROM 客户 AS LiMing, 客户AS Chang WHERE LiMing.客户编号>= Chang.客户编号 GROUP BY LiMing.客户编号, LiMing.公司名称 ORDER BY 序号; GO/* 方法四 建立一个「自动编号」的字段,然后将数据新增至一个区域性暂存数据表, 然后由该区域性暂存数据表中,将数据选取出来,最后删除该区域性暂存数据表 */ SELECT序号= IDENTITY(INT,1,1),管道,程序语言,讲师,资历 INTO #LiMing FROM问券调查一; GO SELECT * FROM #LiMing; GO DROP TABLE #LiMing; GO/* 方法五 使用 SQL Server 2005 独有的ROW_NUMBER() OVER () 语法 搭配 CTE (一般数据表表达式,就是 WITH 那段语法)选取序号2 ~ 4 的数据 */ WITH排序后的图书 AS (SELECT ROW_NUMBER() OVER (ORDER BY 客户编号 DESC) AS 序号, 客户编号,公司名称 FROM 客户) SELECT * FROM 排序后的图书 WHERE序号 BETWEEN 2 AND 4; GO
declare @table table (ID int) insert into @table select 2516 union all select 6644 union all select 2577 union all select 87 SELECT *,(SELECT COUNT(*) FROM @TABLE WHERE ID<=T.ID) AS NUM FROM @TABLE T ORDER BY 2
反正告诉你一个办法,Select * from tb 它其实就是循环
select row_number() over(order by name), * from syscolumns
2000不select inot没办法 ,要不你有一个自增的表导入进去
declare @table table (ID int)
insert into @table
select 2516 union all
select 6644 union all
select 2577 union all
select 87select ROW_NUMBER() OVER(ORDER BY ID) AS rownum ,* from @table/*
rownum ID
-------------------- -----------
1 87
2 2516
3 2577
4 6644
*/
insert into @table
select 2516 union all
select 6644 union all
select 2577 union all
select 87create table #t (rownum int identity(1,1),id int)INSERT INTO #t(id)
select * from @table
select * from #t
drop table #t/*
rownum id
----------- -----------
1 2516
2 6644
3 2577
4 87
*/
2005环境下,确实可以。我也试过了。
insert into @table
select 2516 union all
select 6644 union all
select 2577 union all
select 87
SELECT *,(SELECT COUNT(*) FROM @TABLE WHERE ID<=T.ID) AS NUM FROM @TABLE T ORDER BY 2(所影响的行数为 4 行)ID NUM
----------- -----------
87 1
2516 2
2577 3
6644 4(所影响的行数为 4 行)
DCount不对这个是要排序的。好像和你的不太相符。
WHERE LiMing.客户编号<= Chang.客户编号),
客户编号,公司名称
FROM客户 AS Chang ORDER BY 1;
GO/* 方法二: 使用SQL Server 2005 独有的RANK() OVER () 语法*/
SELECT RANK() OVER (ORDER BY 客户编号 DESC) AS 序号,
客户编号,公司名称
FROM客户;
GO/* 方法三*/
SELECT序号= COUNT(*), LiMing.客户编号, LiMing.公司名称
FROM 客户 AS LiMing, 客户AS Chang
WHERE LiMing.客户编号>= Chang.客户编号
GROUP BY LiMing.客户编号, LiMing.公司名称
ORDER BY 序号;
GO/* 方法四
建立一个「自动编号」的字段,然后将数据新增至一个区域性暂存数据表,
然后由该区域性暂存数据表中,将数据选取出来,最后删除该区域性暂存数据表
*/
SELECT序号= IDENTITY(INT,1,1),管道,程序语言,讲师,资历
INTO #LiMing
FROM问券调查一;
GO
SELECT * FROM #LiMing;
GO
DROP TABLE #LiMing;
GO/*
方法五
使用 SQL Server 2005 独有的ROW_NUMBER() OVER () 语法
搭配 CTE (一般数据表表达式,就是 WITH 那段语法)选取序号2 ~ 4 的数据
*/
WITH排序后的图书 AS
(SELECT ROW_NUMBER() OVER (ORDER BY 客户编号 DESC) AS 序号,
客户编号,公司名称
FROM 客户)
SELECT * FROM 排序后的图书
WHERE序号 BETWEEN 2 AND 4;
GO
insert into @table
select 2516 union all
select 6644 union all
select 2577 union all
select 87
SELECT *,(SELECT COUNT(*) FROM @TABLE WHERE ID<=T.ID) AS NUM FROM @TABLE T ORDER BY 2