ÓÃÓÚ·ÖÒ³ÏÔʾµÄSQLÎÄ

Ô­Ö· http://technet.microsoft.com/ja-jp/library/cc411406.aspx
 
¿´ÁËһƪSQL2005¹ØÓÚTransact-SQLµÄÇ¿»¯¼¼ÄܵĽéÉÜ£¬¾õµÃºÜÓÐÓô¦£¬¼òµ¥×ܽáÁËһϡ£
 
£¨Ò»£©Êý¾Ý¿â×ö³É
USE tempdb – »òÕßн¨Ò»¸öDB
CREATE TABLE SpeakerStats
(
  speaker        VARCHAR(10) NOT NULL PRIMARY KEY,
  track          VARCHAR(10) NOT NULL,
  score          INT         NOT NULL,
  pctfilledevals INT         NOT NULL,
  numsessions    INT         NOT NULL
)
 
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan',     'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron',     'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy',   'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB',  9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe',     'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert',  'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike',    'DB',  8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian',   'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin',   'DB',  7, 25, 4)
 
£¨¶þ£©
SQL2005ϵÄSQLÓï¾ä£¨1£©ºÍ2000ϵģ¨2£©Ö´Ðнá¹ûÒ»ÖÂ
£¨1£©
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,
                           numsessions DESC, speaker) AS rownum,
  speaker, track, score, pctfilledevals, numsessions
from SpeakerStats
ORDER BY score