如何在查询中将NULL替换为0? [英] How to replace NULL with 0 in the query?

问题描述

我有这个查询:

$db->prepare("UPDATE users 
               SET reputation = reputation +
                               (CASE WHEN id = ? THEN 2
                                     WHEN id = ? AND NOT ? THEN 15 END)
              WHERE id IN (?, ?); ")
->execute(array($author_ques_id, $author_ans_id, $isOwnAnswer,
                $author_ans_id, $author_ques_id));

请专注于这一行:

WHEN id = ? AND NOT ? THEN 15 END)

当NOT ?为 false (即$isOwnAnswer = true)时，该用户的reputatuin值将为NULL.因为reputation = reputation + NULL评估reputation = NULL.

When NOT ? is false (in other word $isOwnAnswer = true), the value of reputatuin for such a user will be NULL. Because reputation = reputation + NULL evaluates reputation = NULL.

那么我该如何预防呢?我想当条件为 false 时，reputation不变.

Well how can I prevent of that? I want when that condition is false, then reputation doesn't change.

推荐答案

无需用其他CASE/COALESCE/IFNULL或其他任何东西包装，只需使用CASE EXPRESSION ELSE即可，如果不满足所有条件，则ELSE将被使用.

No need to wrap it with another CASE/COALESCE/IFNULL or anything, just use the CASE EXPRESSION ELSE , in case all the conditions are not met, the ELSE will be used.

UPDATE users 
           SET reputation = reputation +
                            CASE WHEN id = ? THEN 2
                                 WHEN id = ? AND NOT ? THEN 15
                                 ELSE 0
                            END
          WHERE id IN (?, ?);

这篇关于如何在查询中将NULL替换为0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！