sql - 除法结果为零时返回字符串

Question

我有一个这样的 SQL 查询:

SELECT 
u.id,
tu.score/(CASE tu.mo_count  WHEN 0 THEN NULL ELSE tu.mo_count END) AS score_avg
FROM tournament_userscore tu
INNER JOIN users u ON u.id = tu.user_id
WHERE tournament_id = 1
ORDER BY tu.score DESC

当 tu.mo_count 为零时，Null 被替换为零。我如何为这一行设置一个类似 'EMPTY' 的字符串:

tu.score/(CASE tu.mo_count  WHEN 0 THEN NULL ELSE tu.mo_count END) AS score_avg

我的意思是，当除法结果为零 或空 时，我想返回“anything ”字符串。

Answer 1

如果不想使用 CASE，可以使用 NULLIF 和 COALESCE:

SELECT 
  u.id,
  COALESCE(tu.score/NULLIF(tu.mo_count, 0), 0) AS score_avg
FROM tournament_userscore tu
  INNER JOIN users u ON u.id = tu.user_id
WHERE 
  tournament_id = 1
ORDER BY 
  tu.score DESC;