postgresql - Postgres 位掩码分组依据

Question

我声明了以下标志:

0 - None
1 - Read
2 - Write
4 - View

我想编写一个查询，该查询将在此位掩码上分组并获取所使用的每个标志的计数。

person  mask
  a      0
  b      3
  c      7
  d      6

结果应该是:

flag        count
 none        1
 read        2
 write       3
 view        2

如有任何提示，我们将不胜感激。

致克雷格

SELECT  lea.mask as trackerStatusMask,
        count(*) as count
FROM    Live le 
INNER JOIN (
 ... --some guff
) lea on le.xId = lea.xId
WHERE   le.xId = p_xId   
GROUP BY lea.mask;

Answer 1

SQL Fiddle

select
    count(mask = 0 or null) as "None",
    count(mask & 1 > 0 or null) as "Read",
    count(mask & 2 > 0 or null) as "Write",
    count(mask & 4 > 0 or null) as "View"
from t