怎样在视图中插入一列自动增加的列

[inte_sleeper]

有一个表 users 结构如下:
uid int(4) identity(1,1) not null,
ac int(4) not null,
ratio int(4) not null

我现在需要将它们按[ac] DESC,[ratio] DESC排序,而且增加一列自增的量
即假如原表中数据如下:
uid ac ratio
1 11 3
2 16 5
3 16 8
4 33 4
5 3 9

加入一列后,数据如下:
id uid ac ratio
1 4 33 4
2 3 16 8
3 2 16 5
4 1 11 3
5 5 3 9

请问应该怎么写?不是视图也行.只要能用SQL写出来就可以.谢谢.

[xiaoku]

同一张表 里不能使用两个identity 列...

[inte_sleeper]

只要能够从1自动增加就行,不一定非得是identity,有办法吗?

[xiaoku]

oh ...

[inte_sleeper]

SELECT TOP 100000
(SELECT SUM(1)
FROM [users]
WHERE [ac] = [users].[ac]) AS [id], dbo._users.*
FROM dbo.users
ORDER BY ac DESC, ratio DESC
可是得到的数据的id列全是一样的数字...

[xiaoku]

我知道了...
等等...

[xiaoku]

刚才 灵光一现,哈哈...谁知道业是错的...

select (select count(1) from users )-uid ,*
from users
order by [ac] DESC,[ratio] DESC

uid ac ratio
----------- ----------- ----------- -----------
1 4 33 4
2 3 16 8
3 2 16 5
4 1 11 3
0 5 3 9

再想想看看...

[xiaoku]

select (select count(1) from users where (ac>=a.ac) ) as id ,*
from users a
order by [ac] DESC,[ratio] DESC

id uid ac ratio
----------- ----------- ----------- -----------
1 4 33 4
3 3 16 8
3 2 16 5
4 1 11 3
5 5 3 9

[inte_sleeper]

我刚刚看到另外一篇帖子,只要能在视图中增加一列递增或递减的列就可以,也不一定要加1或减1,这样的话可以在这个视图外面用一个查询来实现:select newid=(SELECT COUNT(*) FROM myview WHERE [id]>=(或<=)A.[id]),* FROM myview A

[xiaoku]

那你说说该如何解决?

[xiaoku]

/*
有一个表 users 结构如下:
--drop table users
create table users (
uid int identity(1,1) not null,
ac int not null,
ratio int not null
)
insert into users select 11 , 3
union all select 16, 5
union all select 16, 8
union all select 33, 4
union all select 16 ,10
union all select 2 ,9
union all select 11 ,9

select * from users
*/
select case when (select count(1) from users where a.ac =ac )=1 then
(select count(1) from users where (ac>=a.ac) )
else (select count(1) from users where (ac=a.ac) and ratio >=a.ratio )+(select count(1) from users where (ac>a.ac) ) end
as id ,*
from users a
order by [ac] DESC,[ratio] DESC


id uid ac ratio
----------- ----------- ----------- -----------
1 4 33 4
2 5 16 10
3 3 16 8
4 2 16 5
5 7 11 9
6 1 11 3
7 6 2 9

（所影响的行数为 7 行）


------------------
想想一个晚上终于做好了

[xiaoku]

高兴!

[asdf_jkl]

SELECT indentity(int,1,1) as id, * FROM users
ORDER BY ac DESC, ratio DESC