--> 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',100select *,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 行受影响) */
--> 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',100select *,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 行受影响) */
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 DESCSELECT * FROM #DROP TABLE # /* no counts paixu ---- ----------- ----------- e 100 1 c 90 2 a 10 3 d 2 4 */
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 goselect * from tselect no,counts,paixu=identity(int,1,1) into # from t order by counts descselect * from #
--> 测试数据:@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',100select *,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 行受影响)
*/
--> 测试数据:@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',100select *,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 行受影响)
*/
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 DESCSELECT * FROM #DROP TABLE #
/*
no counts paixu
---- ----------- -----------
e 100 1
c 90 2
a 10 3
d 2 4
*/
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
goselect * from tselect no,counts,paixu=identity(int,1,1) into # from t order by counts descselect * from #