select UName,  

RANK()  over(  ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1))+'%') as tongguo

from

  UQD
group by 

  UName

--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO

--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
---------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        6
--*/



--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
--------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8
--*/

------解决方案--------------------
SQL code
SELECT  UName ,
        RANK() OVER ( ORDER BY LTRIM(SUM(CASE Violation
                                           WHEN '1' THEN 1
                                           WHEN '2' THEN 1
                                           ELSE 0
                                         END) * 100 / COUNT(1)) + '%' ) AS tongguo
FROM    UQD
GROUP BY UName

------解决方案--------------------
探讨


我是2005的啊