mysql - 如何在 "bit field"中最好地执行分组？

Question

借自this question ，假设有三个标志:4 => 读，2 => 写，1 => 执行，表如下所示:

  file_id  |  fsize  |  permissions
-----------+---------+---------------
        1  |  200    |  6    ( <-- 6 = 4 + 2 = read + write)
        2  |  300    |  4    ( <-- 4 = 4 = read)
        3  |  400    |  3    ( <-- 3 = 2 + 1 = write + execute)
        4  |  500    |  1    ( <-- 1 = execute)

考虑到这个字段可以包含多个标志，我需要返回一个由这三个标志分组的结果，如下所示:

 |  fsize  |  permissions
 +---------+---------------
 |  900    |  1    
 |  600    |  2    
 |  500    |  4

执行此操作的最佳方法是什么？

Answer 1

您可以为此使用join:

select p.permission, sum(fsize)
from t join
     (select 1 as permission union all
      select 2 union all
      select 4
     ) p
     on p.permission & t.permissions > 0
group by p.permission
order by p.permission;