合并为零而不是空 [英] Coalesce for zero instead of null

问题描述

我有一个简单的公司表，它不一致地使用列名 turnover 和 revenue.该表目前看起来像这样:

I have a simple company table which inconsistently use column name turnover and revenue. The table currently looks like this:

company, turnover, revenue
A, 10000, 0
B, 0, 2500
C, 0, 3000
4, 23000, 0

我知道如何使用 coalesce 在 null 和 value 之间进行选择，但是要丢弃的值是零而不是 null.

I know how to use coalesce to choose between null and value, but there the value to be discarded is zero instead of null.

我想要的最终结果是:

company, revenue
A, 10000
B, 2500
C, 3000
D, 23000

我想象的查询是:

select company, coalesce_for_zero(turnover, revenue) as revenue from
company_profile;

如何编写一个查询来实现类似合并的结果为零?

How do I write a query that can achieve coalesce-like results for zero?

推荐答案

你可以结合 NULLIF 和 COALESCE:

SELECT company, COALESCE(NULLIF(turnover, 0), revenue) AS revenue FROM company_profile;

NULLIF 如果参数相等则返回 NULL，否则返回第一个参数.

NULLIF returns NULL if it's arguments are equal, and the first argument otherwise.

这篇关于合并为零而不是空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！