CSDN博客

img h_q_p

生成名次的SQL

发表于2004/10/6 14:27:00  794人阅读

分类: SQL相關

create database test1
go
use test1
go
create table Test
(F1 char(10),
 F2 char(10))

--测试表

insert into Test
select 'a' F1,'1' F2
union
select 'b' F1,'2' F2
union
select 'c' F1,'3' F2
union
select 'd' F1,'3' F2
union
select 'e' F1,'4' F2
union
select 'f' F1,'4' F2
union
select 'g' F1,'4' F2
union
select 'h' F1,'7' F2
union
select 'i' F1,'9' F2

--插入数据

select id=identity(int,0,1),f1,f2 into #t from test order by F2 desc

select a.f1,a.f2,a.id+1-cast(id-cc-minn as Char(10)) as  [名次]
from #t a,
(select f2,cc,minn  from
  (select  f2,count(*)as cc,min(id)-count(*) as minn from #t group by f2) t)b
where a.f2=b.f2
order by a.f2 desc

--测试

drop table #t
drop table test

阅读全文
0 0

相关文章推荐

img
取 消
img