select *, ID=row_number()over(order by (select 1)), '固定值'col from 视图
create view V_表 asselect row_number()over(order by (select 1)) as ID,*,'Hi' as 固定列 from 表
select m.* , '固定值' , [递增列] = (select count(1) from (你的查询) n where n.某个能区分大小的列 < n.某个能区分大小的列) + 1 from (你的查询) m
select *, ID=(select count(*)from 视图 where 主鍵列<=t.主鍵列) '固定值'col from 视图 t
SELECT * ,(SELECT COUNT(*) FROM TB WHERE ID<=T.ID)AS NUM ,'FDA' FROM TB T
SELECT ROW_NUMBER() OVER (ORDER BY menuid ASC) AS ROWID,'固定列' as 列名,* from menu
如果是2000 沒有自增列或者主鍵 只能用臨時表了 select *, ID=identity(int,1,1), '固定值'col into # from 视图 go select * from #
--sql 2000select m.* , '固定值' , [递增列] = (select count(1) from (你的查询) n where n.某个能区分大小的列 < n.某个能区分大小的列) + 1 from (你的查询) m表jh03有下列数据: name score aa 99 bb 56 cc 56 dd 77 ee 78 ff 76 gg 78 ff 501. 名次生成方式1,Score重复时合并名次 SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score) FROM jh03 a ORDER BY Place 结果 Name Score Place ---------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 3 ff 76.00 4 bb 56.00 5 cc 56.00 5 ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺 SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1 FROM jh03 a ORDER BY Place 结果 Name Score Place --------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 4 ff 76.00 5 bb 56.00 6 cc 56.00 6 ff 50.00 8--sql 2005用row_number select t.* , '固定值' , [递增列] = row_number() over(order by 某个列) from (你的查询) t
select 固定列,identity(int,1,1) '增长列',* from YourView
create table TT ( Name varchar(20), age int ) insert into TT select 'aa',23 insert into TT select 'bb',24 insert into TT select 'cc',25 insert into TT select 'dd',26 insert into TT select 'ee',27 alter view V_T as select *,'胡' '固定列',(select count(*)+1 from TT where age<T.age) 'ID' from TT Tselect * from V_T Name age 固定列 ID -------------------- ----------- ---- ----------- aa 23 胡 1 bb 24 胡 2 cc 25 胡 3 dd 26 胡 4 ee 27 胡 5
ID=row_number()over(order by (select 1)),
'固定值'col
from 视图
asselect row_number()over(order by (select 1)) as ID,*,'Hi' as 固定列 from 表
from (你的查询) m
ID=(select count(*)from 视图 where 主鍵列<=t.主鍵列)
'固定值'col
from 视图 t
沒有自增列或者主鍵
只能用臨時表了
select *,
ID=identity(int,1,1),
'固定值'col into #
from 视图
go
select * from #
from (你的查询) m表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8--sql 2005用row_number
select t.* , '固定值' , [递增列] = row_number() over(order by 某个列) from (你的查询) t
(
Name varchar(20),
age int
)
insert into TT select 'aa',23
insert into TT select 'bb',24
insert into TT select 'cc',25
insert into TT select 'dd',26
insert into TT select 'ee',27
alter view V_T
as
select *,'胡' '固定列',(select count(*)+1 from TT where age<T.age) 'ID' from TT Tselect * from V_T
Name age 固定列 ID
-------------------- ----------- ---- -----------
aa 23 胡 1
bb 24 胡 2
cc 25 胡 3
dd 26 胡 4
ee 27 胡 5