select UName, RANK() over( ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1))+'%') as tongguofrom UQD
group by UName可是提示'RANK' 不是可以识别的 函数名。
我想实现根据ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1))+'%'排名并且把名次放到查询的表里
rank是2005、2008的
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
--*/2000的参考这个
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
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 tongguofrom UQD
group by UName
我在2008上用下面的语句可运行
select *,排名=DENSE_RANK() over(order by Score desc) from tb