test表结构如下
id,shopID
44 1
45 3
49 4
50 8
53 4
如何写sql语句,才能在查询结果构造字段seq,使结果如下:seq,id,shopID
1 44 1
2 45 3
3 49 4
4 50 8
5 53 4谢谢了,各位
id,shopID
44 1
45 3
49 4
50 8
53 4
如何写sql语句,才能在查询结果构造字段seq,使结果如下:seq,id,shopID
1 44 1
2 45 3
3 49 4
4 50 8
5 53 4谢谢了,各位
seq=row_number()over(order by getdate()),*
from
test
ROW_NUMBER() OVER (ORDER BY ID) AS SEG
,*
FROM TEST
ORDER BY ID
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] int,[shopID] int)
insert [test]
select 44,1 union all
select 45,3 union all
select 49,4 union all
select 50,8 union all
select 53,4select seq=(select COUNT(1)
from test b where b.ID<=a.ID),
* from test a
/*
seq id shopID
---------------------
1 44 1
2 45 3
3 49 4
4 50 8
5 53 4
*/
select id=identity(int,1,1), id,shopID from test
仅当 SELECT 语句中有 INTO 子句时,才能使用 IDENTITY 函数。
alter table test add seq int identity(1,1)