java - 根据列条目类型对值进行排名

Question

我有一个 SQL 查询，它对名为“Total”的列的值进行排名，并在名为“Rank”的计算列中返回结果。查询如下:

String sql ="select s1.Name as 'NAME',s1.Lang as 'LANGUAGE',"
            + "s1.Total as 'TOTAL',COUNT(DISTINCT s2.Total) AS Rank from Type1 s1 JOIN Type1 s2 on(s1.Total<=s2.Total) GROUP BY s1.ID order by s1.Name ASC ";  

    pst=conn.prepareStatement(sql);
    rs=pst.executeQuery();
    table_statistics1.setModel(DbUtils.resultSetToTableModel(rs));

它给出了以下结果:

| NAME  | LANGUAGE | TOTAL | RANK| 
------------------------------------------------------
| james|French  |70.0|2nd | 
| jimmy|English |90  |1st |
|  josh|French  |60  |3rd |
|  john|English |40  |4th |

我想编写一个查询，对总列进行排名，但仅基于语言类型。因此，由于我有两种类型的语言，它将对总列进行排名，并仅返回法语条目和单独排名的值仅适用于英文条目..这样输出将如下所示:

| NAME  | LANGUAGE | TOTAL | RANK| 
------------------------------------------------------ 
| james |French   |70.0    |1st | 
| jimmy |English  |90      |1st |
|josh   |French   |60      |2nd |
|john   |English  |40      |2nd |

所有建议将不胜感激。谢谢。

Answer 1

正常的排名方法是使用 ANSI 标准窗口函数:

select t.Name, t.Lang as LANGUAGE, t.Total,
       dense_rank() over (Partition by t.lang order by t.total) as rnk
from Type t;

在 SQLite 中，您可以执行以下操作:

select t.*,
       (select count(distinct t2.total)
        from t t2
        where t2.lang = t.lang and t2.total <= t.total
       ) as rnk
from Type t;