查找学生的排名 -Sql Compact [英] Finding rank of the student -Sql Compact

问题描述

我有一张这样的桌子:

名称 Mar1 Mar2 Mar3 总计

Name Mar1 Mar2 Mar3 Total

xxx 80 80 80 240

xxx 80 80 80 240

yyy 60 70 50 180

yyy 60 70 50 180

aaa 85 65 75 225

aaa 85 65 75 225

我想根据总数找到学生的排名.我使用 SQL Compact 3.5 .因为我们在 sql server 中有 rank() 函数，所以我们有什么可以找到学生排名的东西吗???当我使用select Total,rank() over (order by total desc) i1从 stmarks 它给出的错误为

I wanted to find the rank of the student based on total. I using SQL Compact 3.5 . As we have rank() function in sql server do we have something with which we can find the students rank??? When I used "select Total,rank() over (order by total desc) i1 from stmarks " it's giving error as

"主要错误 0x80040E14，次要错误 25501

" Major Error 0x80040E14, Minor Error 25501

select Total,rank() over (order by total desc) i1从 stmarks解析查询时出错.[令牌行号=1，令牌行偏移=21，令牌错误=结束]"

select Total,rank() over (order by total desc) i1 from stmarks There was an error parsing the query. [ Token line number = 1,Token line offset = 21,Token in error = over ] "

Sql Compact 支持 rank() 还是有其他方法???

Do Sql Compact support rank() over or is there any another way???

推荐答案

根据搜索结果似乎 over() 不适用于 sql-compact.所以我尝试了 here 中的自连接概念.只是我修改了查询，以便它可以正确处理重复值.去掉等号.修改后的查询是:

According to the search result it seems over() doesn't work with sql-compact. So I tried the self join concept as in here. Just I modified the query so that it works with duplicate values properly . Remove the equal to sign. The modified query is :

SELECT a1.Name, a1.Total, COUNT(a2.Total) Rank
  FROM StMarks a1, StMarks a2
 WHERE a1.Total < a2.Total or (a1.Total=a2.Total and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Total
ORDER BY a1.Total DESC, a1.Name DESC;

它现在很好用.

这篇关于查找学生的排名 -Sql Compact的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！