如何在复杂查询中将 null 替换为 0

发布于 2024-10-10 14:57:55 字数 449 浏览 3 评论 0原文

我想当总值为空时将其替换为 0

以下是查询：

SELECT DISTINCT(location),  (
    SELECT  Count(a.location) as total
    FROM table_fo a   
    LEFT JOIN  table_info b ON a.TRADEID = b.TRADEID AND   a.asofdate = b.asofdate
    WHERE (b.TERMSTATUS <> 'TRAN' OR b.TERMSTATUS is NULL)  AND b.asofdate = '20110105' AND a.location = pfo.location
 GROUP BY a.LOCATION  
)   AS total   
FROM table_fo  pfo
WHERE asofdate = '20110105';

原文

I would like to replace the total value to 0 when it is null

Here is the query:

SELECT DISTINCT(location),  (
    SELECT  Count(a.location) as total
    FROM table_fo a   
    LEFT JOIN  table_info b ON a.TRADEID = b.TRADEID AND   a.asofdate = b.asofdate
    WHERE (b.TERMSTATUS <> 'TRAN' OR b.TERMSTATUS is NULL)  AND b.asofdate = '20110105' AND a.location = pfo.location
 GROUP BY a.LOCATION  
)   AS total   
FROM table_fo  pfo
WHERE asofdate = '20110105';

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

肤浅与狂妄 2024-10-17 14:57:55

您可以使用 ISNULL 函数。

以下是您将如何使用该函数（对于 SQL Server）。

ISNULL(columnName, 0)

You can use the ISNULL function.

Here is how you will use the function (for SQL Server).

ISNULL(columnName, 0)

回复收藏 0 原文

蓝颜夕 2024-10-17 14:57:55

我想更换总计
为空时值为0

这是不可能的情况，因为：

COUNT 函数始终返回一个整数。结果不能为 NULL！

至于将表达式合并为某个默认值（以防 NULL），所有主要数据库中都有执行此操作的函数（例如：COALESCE、NVL、ISNULL、IFNULL）。典型用途是

FUNCTION_NAME(ExpressionThatMayBeNULL, DefaultWhenNull)

有关具体信息，您应该查阅数据库制造商文档（您可以在线找到它）。

I would like to replace the total
value to 0 when it is null

This is an impossible situation because:

The COUNT function always returns an integer. The result cannot be NULL!

As for coalescing an expression to a certain default in case it is NULL there are functions that do this in all major databases (ex.: COALESCE, NVL, ISNULL, IFNULL). The typical use is

FUNCTION_NAME(ExpressionThatMayBeNULL, DefaultWhenNull)

For specifics you should consult you database manufacturers documentation (you can find it online).

回复收藏 0 原文

殤城〤 2024-10-17 14:57:55

COALESCE 将在 PL/SQL (Oracle) 和 T-SQL (SQL Server) 中执行此操作

，语法为 COALESCE(field1, field2[, fieldN]) - 它将选择左侧第一列具有非空值。

修改您的查询：

SELECT DISTINCT(location),  COALESCE((
    SELECT  Count(a.location) as total
    FROM table_fo a   
    LEFT JOIN  table_info b ON a.TRADEID = b.TRADEID AND   a.asofdate = b.asofdate
    WHERE (b.TERMSTATUS <> 'TRAN' OR b.TERMSTATUS is NULL)  AND b.asofdate = '20110105' AND a.location = pfo.location
 GROUP BY a.LOCATION  
),0)   AS total   
FROM table_fo  pfo
WHERE asofdate = '20110105';

COALESCE will do this in PL/SQL (Oracle) and T-SQL (SQL Server)

Syntax is COALESCE(field1, field2[, fieldN]) - it will select the first column from the left to have a non-null value.

Modifying the query you had:

SELECT DISTINCT(location),  COALESCE((
    SELECT  Count(a.location) as total
    FROM table_fo a   
    LEFT JOIN  table_info b ON a.TRADEID = b.TRADEID AND   a.asofdate = b.asofdate
    WHERE (b.TERMSTATUS <> 'TRAN' OR b.TERMSTATUS is NULL)  AND b.asofdate = '20110105' AND a.location = pfo.location
 GROUP BY a.LOCATION  
),0)   AS total   
FROM table_fo  pfo
WHERE asofdate = '20110105';

回复收藏 0 原文