SQL数据排序(转贴)

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

--测试表
CREATETABLETest(F1char(10),F2char(10))

--插入数据
INSERTINTOTest
SELECT'a'F1,'1'F2
UNION
SELECT'b'F1,'2'F2
UNION
SELECT'c'F1,'4'F2
UNION
SELECT'd'F1,'3'F2
UNION
SELECT'e'F1,'4'F2
UNION
SELECT'f'F1,'5'F2
UNION
SELECT'g'F1,'4'F2
UNION
SELECT'h'F1,'7'F2
UNION
SELECT'i'F1,'9'F2

---排名次
--方法1
SELECTa.*,(SELECTCOUNT(*)FROMtestbWHEREb.F2>a.F2)+1ASminciFROMtestaORDERBYminci
--方法2
SELECTid=IDENTITY(int,0,1),f1,f2INTO#tFROMtestORDERBYF2DESC
SELECTa.f1,a.f2,a.id+1-cast(id-cc-minnASChar(10))AS[名次]
FROM#ta,(SELECTf2,cc,minnFROM(SELECTf2,COUNT(*)AScc,MIN(id)-COUNT(*)ASminnFROM#tGROUPBYf2)t)b
WHEREa.f2=b.f2
ORDERBYa.f2DESC

--删除表
DROPTABLE#t
DROPTABLEtest


<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>