sql - 如何将当前值替换为定义的字符串

Question

查询结果

select ( LENGTH(testid) - LENGTH(replace(testid, '/', '')) ) as num_slashes,
 count(*) from test.1 where isdeleted = false  group by ( LENGTH(testid) - LENGTH(replace(testid, '/', '')) )
 ORDER BY COUNT(*) 

从数据库返回：

我想将 0、1、2 替换为 master1、master2、master3。

我应该使用什么查询，所以查询结果将是

标签： sql

Answer 1

我将通过使用派生表进行计算num_slashes，然后使用CASE表达式将该值转换为字符串来简化您的查询：

SELECT CASE num_slashes WHEN 0 THEN 'master1'
                        WHEN 1 THEN 'master2'
                        ELSE 'master3'
       END AS number_slashes,
       COUNT(*) AS count
FROM (
  SELECT ( LENGTH(testid) - LENGTH(replace(testid, '/', '')) ) AS num_slashes
  FROM test.1
  WHERE isdeleted = false
) t
GROUP BY num_slashes
ORDER BY num_slashes