?sql

[dadihongchang]

no counts
a 10
c 90
d 2
e 100
得到形式？
no counts paixu
e 100 1
c 90 2
a 10 3
d 2 4

[dobear_0922]

Row_Number

[csdyyr]

select *,id=identity(int,1,1) into # from tb

[dobear_0922]

--> By dobear_0922(小熊) 2008-11-07 17:11:02
--> 测试数据：@t
declare @t table([no] varchar(1),[counts] int)
insert @t
select 'a',10 union all
select 'c',90 union all
select 'd',2 union all
select 'e',100

select *,paixu=ROW_NUMBER() OVER(order by [counts] desc) 
from @t order by [counts] desc

/*
no   counts      paixu
---- ----------- --------------------
e    100         1
c    90          2
a    10          3
d    2           4

(4 行受影响)
*/

[dobear_0922]

--> By dobear_0922(小熊) 2008-11-07 17:11:02
--> 测试数据：@t
declare @t table([no] varchar(1),[counts] int)
insert @t
select 'a',10 union all
select 'c',90 union all
select 'd',2 union all
select 'e',100

select *,paixu=(select count(*) from @t where [counts]>=t.[counts]) 
from @t t order by [counts] desc

/*
no   counts      paixu
---- ----------- --------------------
e    100         1
c    90          2
a    10          3
d    2           4

(4 行受影响)
*/

[csdyyr]

DECLARE @TB TABLE(no VARCHAR(2),  counts INT)
INSERT @TB
SELECT 'a',  10 UNION ALL 
SELECT 'c',  90 UNION ALL 
SELECT 'd',  2 UNION ALL 
SELECT 'e',  100

 
SELECT *,paixu=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY counts DESC

SELECT * FROM #

DROP TABLE #
/*
no   counts      paixu       
---- ----------- ----------- 
e    100         1
c    90          2
a    10          3
d    2           4
*/

[hsie168518]

[Quote=引用 4 楼 dobear_0922 的回复:]
SQL code--> By dobear_0922(小熊) 2008-11-07 17:11:02
--> 测试数据：@t
declare @t table([no] varchar(1),[counts] int)
insert @t
select 'a',10 union all
select 'c',90 union all
select 'd',2 union all
select 'e',100

select *,paixu=(select count(*) from @t where [counts]>=t.[counts])
from @t t order by [counts] desc

/*
no counts paixu
---- ----------- --------------------
e …
[/Quote]
正解

[pengxuan]

if object_id('t') is not null
   drop table t
go
create table t(no varchar(10),counts int)
go
insert into t
select 'a',10 union all
select 'b',90 union all
select 'c',2 union all
select 'd',100
go

select * from t

select no,counts,paixu=identity(int,1,1) into # from t order by counts desc

select * from #

[jimoshatan]

没得写了

[surpass63]

[Quote=引用 3 楼 dobear_0922 的回复:]
SQL code--> By dobear_0922(小熊) 2008-11-07 17:11:02
--> 测试数据：@t
declare @t table([no] varchar(1),[counts] int)
insert @t
select 'a',10 union all
select 'c',90 union all
select 'd',2 union all
select 'e',100

select *,paixu=ROW_NUMBER() OVER(order by [counts] desc)
from @t order by [counts] desc

~ …
[/Quote]