我有以下的查询
SELECT DISTINCT
pt.incentive_marketing,
pt.incentive_channel,
pt.incentive_advertising
FROM test.pricing pt
WHERE pt.contract_id = 90000
group by 1,2,3
order by pt.incentive_marketing;
上述查询返回的结果如附图所示:
然而我想使用 COALESCE 将所有的空值替换为 0,请告诉我如何在上述 SELECT 查询中实现此目的。
现在我使用 COALESCE 修改了查询如下:
SELECT
COALESCE( pt.incentive_marketing, '0' ),
COALESCE(pt.incentive_channel,'0'),
COALESCE( pt.incentive_advertising,'0')
FROM test.pricing pt
WHERE pt.contract_id = 90000
group by 1,2,3
结果如图2所示。
我仍然收到一行带有空值的数据。
COALESCE
将空值替换为零了吗?出了什么问题? - Evgeniy Chekandistinct
和group by
没有意义。group by 1,2,3
已经使所有列都不重复了,distinct
运算符也是如此。 - user330315