mysql - 当行值为空时如何更改返回值

Question

我在 DA 类 (Java) 中有一条 SQL 语句。 getStagescore 方法用于获取特定实习的所有分数(方法为荷兰语)。我意识到有些分数是null。

public ArrayList<Stagescore> getStagescore(int stageId) {
    ArrayList<Stagescore> stagescores = new ArrayList<Stagescore>();
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        statement = connection.prepareStatement("SELECT * FROM stagescore INNER JOIN score ON stagescore.waarde = score.waarde AND stagescore.criteriumId = score.criteriumId inner join criterium ON score.criteriumId = criterium.criteriumId WHERE stagescore.stageId = ?");
        statement.setInt(1, stageId);
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            Stagescore stagescore = new Stagescore();
            stagescore.setStagescoreId(resultSet.getInt("stagescore.stagescoreId"));
            stagescore.setStageId(resultSet.getInt("stagescore.stageId"));
            stagescore.setCriteriumId(resultSet.getInt("stagescore.criteriumId"));

            stagescore.setValue(resultSet.getInt("stagescore.value"));


            Score score = new Score();
            score.setCriteriumId(resultSet.getInt("score.criteriumId"));
            score.setScorebetekenis(resultSet.getString("score.scorebetekenis"));
            score.setValue(resultSet.getInt("score.value"));

            Criterium criterium = new Criterium();
            criterium.setCriteriumId(resultSet.getInt("criterium.criteriumId"));
            criterium.setCriterium(resultSet.getString("criterium.criterium"));

            score.setCriterium(criterium);
            stagescore.setScore(score);

            stagescores.add(stagescore);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            statement.close();
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
        }
    }

问号是针对给定的外部值。有时 stagescore.value 的值为空...当 stagescore.value 为空时，如何将其更改为 0？

Answer 1

您可以使用coalesce()(ANSI 标准函数):

SELECT *
FROM stagescore inner join
     score
     on coalesce(stagescore.value, 0) = score.value and
        stagescore.criteriumId = score.criteriumId inner join
     criterium
     on score.criteriumId = criterium.criteriumId
where stagescore.stageId = ?