如何在SQL中按列中单独设置的位对查询结果分组？

当然有办法实现这种需求！你想要的是忽略数值的整体组合，单独统计每一个二进制位被设置（即值为1）的次数，核心思路是通过位运算把每个数值的每一位拆出来单独处理，再分组统计。下面我用具体的SQL示例来演示：

假设我们有一张表numbers，其中val列包含1到10的数值（对应二进制：0001到1010），我们要统计每一位被设置的次数。

实现步骤与SQL示例 #

1. 基础查询（固定位数场景） #

如果你的数值范围是确定的（比如这里最大是10，二进制占4位），可以用以下SQL（适配MySQL/PostgreSQL等主流数据库）：

SELECT 
  CONCAT('位', bit_position) AS 位信息,
  COUNT(*) AS 被设置次数
FROM (
  -- 生成需要检查的所有二进制位位置（从0开始计数，对应1、2、4、8）
  SELECT 0 AS bit_position UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
) bits
CROSS JOIN numbers
-- 判断当前数值的对应位是否为1
WHERE (numbers.val & (1 << bits.bit_position)) != 0
GROUP BY bit_position
ORDER BY bit_position;

2. 关键逻辑解释 #

• 生成位列表：子查询bits列出了我们需要检查的所有二进制位（0到3，因为10的二进制是1010，最高位是第3位）。
• 笛卡尔积关联：CROSS JOIN让每个数值和每个位做组合，这样每个数值的每一位都能被单独校验。
• 位运算判断：1 << bit_position会生成对应位为1的掩码（比如位0对应1，位1对应2，位2对应4），用val & 掩码做位与操作，结果不为0就说明该位是1。
• 分组统计：最后按位位置分组，就能得到每一位被设置的总次数。

3. 预期结果验证 #

按照你给出的1-10的数值，运行上面的查询会得到：

完全符合预期：位0对应数值1、3、5、7、9；位1对应2、3、6、7、10；位2对应4、5、6、7；位3对应8、9、10。

扩展：动态适配任意数值范围 #

如果你的数值范围不确定，可以先计算最大数值的二进制位数，再动态生成位列表。比如在MySQL中可以这样写：

WITH max_val AS (SELECT MAX(val) AS max_num FROM numbers),
bits AS (
  SELECT 0 AS bit_position
  UNION ALL
  SELECT bit_position + 1 FROM bits, max_val
  WHERE (1 << (bit_position + 1)) <= max_num
)
SELECT 
  CONCAT('位', bit_position) AS 位信息,
  COUNT(*) AS 被设置次数
FROM bits
CROSS JOIN numbers
WHERE (numbers.val & (1 << bits.bit_position)) != 0
GROUP BY bit_position
ORDER BY bit_position;

这个写法会自动根据表中最大数值，生成需要检查的所有位，不用手动指定位数。

内容的提问来源于stack exchange，提问作者Irfy