这样就得到百分位了 declare @f float set @f=123123.213 print @f-convert(int,@f)
CREATE TABLE #Score(StudentID int NOT NULL PRIMARY KEY CLUSTERED, Score int NOT NULL)INSERT INTO #Score SELECT StudentID = n, Score = ABS(CHECKSUM(NEWID())) % 600 FROM Nums WHERE n BETWEEN 1 AND 2000SELECT *, 名次 = RANK() OVER(ORDER BY Score DESC), 成绩百分位 = CAST(100.0 * (SELECT COUNT(*) FROM #Score _t WHERE _t.Score <= t.Score) / (SELECT COUNT(*) FROM #Score) AS decimal(5,2)) FROM #Score t
declare @f float
set @f=123123.213
print @f-convert(int,@f)
CREATE TABLE #Score(StudentID int NOT NULL PRIMARY KEY CLUSTERED, Score int NOT NULL)INSERT INTO #Score
SELECT StudentID = n, Score = ABS(CHECKSUM(NEWID())) % 600
FROM Nums
WHERE n BETWEEN 1 AND 2000SELECT *,
名次 = RANK() OVER(ORDER BY Score DESC),
成绩百分位 = CAST(100.0 *
(SELECT COUNT(*) FROM #Score _t WHERE _t.Score <= t.Score) /
(SELECT COUNT(*) FROM #Score) AS decimal(5,2))
FROM #Score t
排序时,可不考虑百分位.
你可以用两个字段处理,一个模拟百分位,一个用来排序就OK了吧.