case-when的重叠条件

Question

我有以下疑问：

SELECT case     
 when tbl.id % 2 = 0 then 'mod-2'
 when tbl.id % 3 = 0 then 'mod-3'
 when tbl.id % 5 = 0 then 'mod-5'
 else 'mod-x'
 end as odds, tbl.id from some_xyz_table tbl;

如果表的Id为admin，则返回的输出为(从pg- 5,6,7复制)：

"mod-5";5
"mod-2";6
"mod-x";7

但是，在这里我可以看到6可以被2和3整除。我的预期输出是：

"mod-5";5
"mod-2";6
"mod-3";6   <-- this
"mod-x";7

有没有办法修改这个查询来获得这样的输出？对我来说，任何替代方案都行。

Answer 1

您可以使用UNION查询执行此操作，编辑将其更改为使用UNION ALL

SELECT 'mod-5', id FROM tbl -- divisible by 5
WHERE id %5 = 0
UNION ALL
SELECT 'mod-2', id FROM tbl -- divisible by 2
WHERE id %2 = 0
UNION ALL
SELECT 'mod-3', id FROM tbl -- divisible by 3
WHERE id %3 = 0
UNION ALL
SELECT 'mod-x',id FROM tbl  -- not divisible by 5,3 or 2
WHERE id %5 <> 0 AND id%2 <> 0 AND id % 3 <> 0