ÇósqlÖÐÔö¼ÓÒ»×ÔÔöÐòºÅ

ÎÒÏÖÔÚÓбí

ѧУ ¿¼ºÅ  ×Ü·Ö
A    101  78
B    102  88
C    103  100
A    104  100
B    105  20
C    106  44
D    107  23

ÏëµÃµ½Ï±í

ѧУ ¿¼ºÅ  ×Ü·Ö  ×ÜÅÅÃû УÄÚÅÅÃû
A    104  100    1        1
C    103  100    2        1
B    102  88    3        1

¸ÃÈçºÎдÄØ£¬×îºÃÄÜÒ»ÌõÓï¾äʵÏÖ¡£×¢ÒâÕâÀïµÄÅÅÃû²»Òª²¢Áеİ¡£¬Ïàͬ³É¼¨µÄË­ÏÈË­ºó¶¼Ã»ÓйØϵ¡£

---

½»¸øÂ¥ÏÂ

---

SQL code:
select *,row_number() over(order by ×Ü·Ö) as rownum
from tb A
where not exits (select 1 from tb B where A.ѧУ=B.ѧУ and A.×Ü·Ö<B.×Ü·Ö)

---

Â¥ÉϵÄÎÒÊÇsqlserver 2000

---

SQL2005 ±È較ºÃŪ£¬2000µÄ話£¬Òª½è臨時±íÁË

SQL code:

select *,
total=row_number() over (order by ×Ü·Ö desc),
xiaonei= row_number() over(partition by ѧУ order by ×Ü·Ö desc)
from T
order by total,xiaonei

---

SQL code

---

Code hi