mysql - 基于按位运算条件的列求和

Question

我有下表

|ID  |Types|Amount|
|-----------------|
|1   |1    |12    |
|2   |3    |3     |
|3   |4    |5     |
|4   |5    |2     |
|-----------------|

类型是基于类型值按位或的值。

可以说:

• 类型 0 为 1 (1<<0)
• 类型 1 为 2 (1<<1)
• 类型 2 为 4 (1<<2)

我想要实现的是按类型分组的金额的SUM。

该表的结果应如下所示:

Type0: 17 (12+3+2, from ID 1,2,4)
Type1: 3 (from ID 2)
Type2: 7 (5+2 from ID 3,4)

是否可以在单个 sql 事务中完成此操作？

Answer 1

如果您还没有位掩码，您可以使用 union 为您的位掩码创建一个表(我认为您应该创建一个表，但这不是这个答案的重点)，然后使用按位逻辑将它们连接在一起，然后按 type_name 进行分组，以获得每种类型的总计。

SELECT
    type_name
  , SUM(Amount)
FROM (
    SELECT 'type 0' AS type_name, 1 AS type_bitmask
    UNION
    SELECT 'type 1',2
    UNION
    SELECT 'type 2',4
) AS derived_types
JOIN [Your amounts table] AS amounts_table
    ON
    amounts_table.Types & derived_types.type_bitmask = derived_types.type_bitmask
GROUP BY
    type_name

请注意，这可以缩短:

SELECT 1<<x.types type
     , SUM(y.amount) total
  FROM 
     ( SELECT 0 types
        UNION
       SELECT 1
        UNION
       SELECT 2
     ) x
  JOIN my_table y
    ON y.types & 1<<x.types = 1<<x.types
 GROUP 
    BY 1<<x.types