如何在Java代码中向PREFERENCE_SCORE表的RANKING字段插入排名

看起来你需要根据计算出的SCORE（即fnis/(fnis+fpis)）生成排名并插入到RANKING字段里，这里有两种实用方案，你可以根据数据量和场景选择：

方案一：内存中计算排名后批量插入 #

适合数据量不大的场景，先把所有数据查询出来计算好分数，在内存里完成排序和排名分配，最后批量插入目标表。

修改后的完整代码示例：

// 1. 先查询所有数据并暂存到列表中
String query = "SELECT ID, FPIS, FNIS FROM db_housekeep";
ResultSet rsp = stats.executeQuery(query);
List<ScoreRecord> records = new ArrayList<>();

while (rsp.next()) {
    int id = rsp.getInt(1);
    double fpis = rsp.getDouble(2);
    double fnis = rsp.getDouble(3);
    double score = fnis / (fnis + fpis);
    records.add(new ScoreRecord(id, score));
}

// 2. 根据SCORE降序排序（如果需要升序排名，改成Double.compare(a.getScore(), b.getScore())）
Collections.sort(records, (a, b) -> Double.compare(b.getScore(), a.getScore()));

// 3. 为每条记录分配排名，处理同分情况
int currentRank = 1;
for (int i = 0; i < records.size(); i++) {
    ScoreRecord record = records.get(i);
    // 如果当前记录和前一条分数相同，排名保持一致
    if (i > 0 && Double.compare(record.getScore(), records.get(i-1).getScore()) == 0) {
        record.setRanking(currentRank);
    } else {
        currentRank = i + 1;
        record.setRanking(currentRank);
    }
}

// 4. 批量插入到PREFERENCE_SCORE表
String sql = "INSERT INTO PREFERENCE_SCORE (ID, SCORE, RANKING) VALUES (?, ?, ?)";
try (PreparedStatement prSt = conn.prepareStatement(sql)) {
    for (ScoreRecord record : records) {
        prSt.setInt(1, record.getId());
        prSt.setDouble(2, record.getScore());
        prSt.setInt(3, record.getRanking());
        prSt.addBatch();
    }
    prSt.executeBatch();
}

// 辅助类，用来临时存储ID、分数和排名
class ScoreRecord {
    private int id;
    private double score;
    private int ranking;

    public ScoreRecord(int id, double score) {
        this.id = id;
        this.score = score;
    }

    // getter和setter方法
    public int getId() { return id; }
    public double getScore() { return score; }
    public void setRanking(int ranking) { this.ranking = ranking; }
    public int getRanking() { return ranking; }
}

方案二：利用数据库窗口函数生成排名（推荐大数据量场景） #

如果数据量较大，内存排序效率低，可以先插入ID和SCORE，再借助数据库的窗口函数自动计算并更新排名，让数据库处理排序逻辑。

分两步实现：

1. 先插入ID和计算好的SCORE

String insertSql = "INSERT INTO PREFERENCE_SCORE (ID, SCORE) VALUES (?, ?)";
try (PreparedStatement prSt = conn.prepareStatement(insertSql)) {
    String query = "SELECT ID, FPIS, FNIS FROM db_housekeep";
    ResultSet rsp = stats.executeQuery(query);
    while (rsp.next()) {
        int id = rsp.getInt(1);
        double fpis = rsp.getDouble(2);
        double fnis = rsp.getDouble(3);
        double score = fnis / (fnis + fpis);
        
        prSt.setInt(1, id);
        prSt.setDouble(2, score);
        prSt.addBatch();
    }
    prSt.executeBatch();
}

2. 用窗口函数更新排名（以MySQL为例，其他数据库如PostgreSQL、SQL Server语法类似）

String updateRankSql = "UPDATE PREFERENCE_SCORE ps " +
                       "JOIN (" +
                       "    SELECT ID, RANK() OVER(ORDER BY SCORE DESC) AS new_rank " +
                       "    FROM PREFERENCE_SCORE" +
                       ") ranked ON ps.ID = ranked.ID " +
                       "SET ps.RANKING = ranked.new_rank";
try (Statement stmt = conn.createStatement()) {
    stmt.execute(updateRankSql);
}

小提示：

• RANK()：同分同排名，下一个排名会跳过（比如1,2,3,3,5）
• ROW_NUMBER()：同分也按顺序分配不同排名（比如1,2,3,4,5）
• DENSE_RANK()：同分同排名，下一个排名不跳过（比如1,2,3,3,4）
  可以根据业务需求选择对应的函数。

额外注意事项 #

• 原代码里的SQL表名写的是PREFERENCE_SCORE_，但你的实际表是PREFERENCE_SCORE，记得修正拼写错误。
• 比较浮点数时，尽量用Double.compare()而不是直接==，避免精度问题导致的判断错误。
• 批量插入时使用addBatch()和executeBatch()能大幅提升插入性能。

内容的提问来源于stack exchange，提问作者DonQuixote