sql - 将 NULL 替换为连接函数 SQL 中的自定义文本

Question

我想把属于同一个id的输出拼接起来，所以我用了下面的函数，但是我也想用自定义文本替换空值，怎么做才对呢？

GROUP_CONCAT(IFNULL(ps_state.name, 'No-State') SEPARATOR ', ')

如果需要，所有代码：

SELECT DISTINCT ps_customer.id_customer AS CustomersId, ps_customer.email AS CustomersName, GROUP_CONCAT(IFNULL(ps_state.name, 'No') SEPARATOR ', ') AS StatesName, 
GROUP_CONCAT(ps_country_lang.name SEPARATOR ', ') AS CountryName 
FROM ps_customer
INNER JOIN ps_address ON ps_customer.id_customer=ps_address.id_customer
LEFT JOIN ps_state ON ps_state.id_state=ps_address.id_state
INNER JOIN ps_country_lang ON ps_country_lang.id_country=ps_address.id_country
WHERE ps_country_lang.id_lang=1
GROUP BY ps_customer.id_customer, ps_state.name

标签： sqlnullconcatenation

Answer 1

Your code basically looks right but I would use coalesce():

GROUP_CONCAT(coalesce(ps_state.name, 'No-State') SEPARATOR ', ')

You might also want to order within the GROUP_CONCAT(), if there is an ordering that you want or expect.