select id,numcol1,numcol2,和=numcol1+numcol2,名次= (select count(1) from table1 a where a.numcol1+a.numcol2>b.numcol1+b.numcol2) from table1 b order by numcol1+numcol2
create table t(id int,numcol1 decimal(8,1),numcol2 decimal(8,1)) insert into t select 1,23,12 union all select 2,90,2 union all select 3,2.5,8 select *,numcol1+numcol2 as '和', '名次'=(select count(1)+1 from t as t1 where t1.numcol1+t1.numcol2>t.numcol1+t.numcol2) from t order by 和 結果: id numcol1 numcol2 和 名次 ----------- ---------- ---------- ----------- ----------- 3 2.5 8.0 10.5 3 1 23.0 12.0 35.0 2 2 90.0 2.0 92.0 1
select id,numcol1,numcol2,和=numcol1+numcol2,名次= (select count(1) from table1 a where a.numcol1+a.numcol2>=b.numcol1+b.numcol2) from table1 b order by (select count(1) from table1 a where a.numcol1+a.numcol2>=b.numcol1+b.numcol2)看看这样排序行不行呢?
--借用下楼上数据 declare @t table (id int,numcol1 decimal(8,1),numcol2 decimal(8,1)) insert into @t select 1,23,12 union all select 2,90,2 union all select 3,2.5,8 union all select 1,23,12 --查询 select *,numcol1+numcol2 as '和', '名次'=(select count(distinct(numcol1+numcol2))+1 from @t as t1 where t1.numcol1+t1.numcol2>t.numcol1+t.numcol2) from @t T order by numcol1+numcol2 desc--结果 id numcol1 numcol2 和 名次 ----------- ---------- ---------- ----------- ----------- 2 90.0 2.0 92.0 1 1 23.0 12.0 35.0 2 1 23.0 12.0 35.0 2 3 2.5 8.0 10.5 3
这种情况!你就只有用临时表了!借楼上的楼上的楼上的数据了! select id,numcol1,numcol2,和=numcol1+numcol2,名次=identity(int,1,1) into # from t b order by (select count(1) from t a where a.numcol1+a.numcol2>=b.numcol1+b.numcol2 and a.id>b.id)select * from #drop table # drop table t id numcol1 numcol2 和 名次 ----------- ---------- ---------- ----------- ----------- 4 90.0 2.0 92.0 1 2 90.0 2.0 92.0 2 3 2.5 8.0 10.5 3 1 23.0 12.0 35.0 4(所影响的行数为 4 行)
(select count(1) from table1 a where a.numcol1+a.numcol2>b.numcol1+b.numcol2)
from table1 b
order by numcol1+numcol2
insert into t select 1,23,12
union all select 2,90,2
union all select 3,2.5,8
select *,numcol1+numcol2 as '和',
'名次'=(select count(1)+1 from t as t1 where t1.numcol1+t1.numcol2>t.numcol1+t.numcol2)
from t order by 和
結果:
id numcol1 numcol2 和 名次
----------- ---------- ---------- ----------- -----------
3 2.5 8.0 10.5 3
1 23.0 12.0 35.0 2
2 90.0 2.0 92.0 1
谢谢了,我还想问一下
你的名次是从0开始的啊,我想以1开始
还有如果有两条记录的和是相同的我就要以相同的名次表示,这该怎样实现呢
(select count(1) from table1 a where a.numcol1+a.numcol2>=b.numcol1+b.numcol2)
from table1 b
order by (select count(1) from table1 a where a.numcol1+a.numcol2>=b.numcol1+b.numcol2)看看这样排序行不行呢?
你们的结果都实现相同的和有相同的名次了,但也缺少了名次:如果有 4 记录,有两条的和相同并且为最高,则名次只显示 :1,1,3,4 .这样一来就没有第2名了
declare @t table (id int,numcol1 decimal(8,1),numcol2 decimal(8,1))
insert into @t select 1,23,12
union all select 2,90,2
union all select 3,2.5,8
union all select 1,23,12 --查询
select *,numcol1+numcol2 as '和',
'名次'=(select count(distinct(numcol1+numcol2))+1 from @t as t1 where t1.numcol1+t1.numcol2>t.numcol1+t.numcol2)
from @t T
order by numcol1+numcol2 desc--结果
id numcol1 numcol2 和 名次
----------- ---------- ---------- ----------- -----------
2 90.0 2.0 92.0 1
1 23.0 12.0 35.0 2
1 23.0 12.0 35.0 2
3 2.5 8.0 10.5 3
select id,numcol1,numcol2,和=numcol1+numcol2,名次=identity(int,1,1) into #
from t b
order by (select count(1) from t a where a.numcol1+a.numcol2>=b.numcol1+b.numcol2 and a.id>b.id)select * from #drop table #
drop table t
id numcol1 numcol2 和 名次
----------- ---------- ---------- ----------- -----------
4 90.0 2.0 92.0 1
2 90.0 2.0 92.0 2
3 2.5 8.0 10.5 3
1 23.0 12.0 35.0 4(所影响的行数为 4 行)