mysql - 如何在我的 sql 表中获取数据时显示 0 而不是 NULL

Question

我正在从 mysql 表中获取数据，它工作正常，但它在两个字段中为 Status 显示 NULL。我想要显示 0 而不是 NULL。

这是我的查询:

SELECT ml.GroupID, ml.GroupTitle, ml.GroupDescription, ml.GroupCreatedDateTime, ml.GroupOwnerUserID, ml.ApprovalNeeded, ml.GroupStatus, ml.OrganizationCode, cat.Status
FROM GroupsMaster AS ml
LEFT JOIN UserGroupsMapping cat ON cat.GroupID = ml.GroupID
WHERE ml.OrganizationCode = ?

Answer 1

使用COALESCE运算符(operator)

   COALESCE(cat.Status, 0)


SELECT 
 ml.GroupID, 
 ml.GroupTitle, 
 ml.GroupDescription, 
 ml.GroupCreatedDateTime, 
 ml.GroupOwnerUserID, 
 ml.ApprovalNeeded, 
 ml.GroupStatus, 
 ml.OrganizationCode, 
 COALESCE(cat.Status, 0) as Status 
FROM GroupsMaster AS ml 
LEFT JOIN UserGroupsMapping cat ON cat.GroupID = ml.GroupID 
WHERE ml.OrganizationCode = 'ibm'