我有下表|ID |Types|Amount||-----------------||1 |1 |12 ||2 |3 |3 ||3 |4 |5 ||4 |5 |2 ||-----------------|Types是基于类型值的按位或的值。可以说:Type0为1(1 Type1是2(1 Type2是4(1 我想实现的是按类型分组的金额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事务中执行此操作? 最佳答案 如果您还没有一个位掩码,则可以使用并集为您的表创建一个表(我认为您应该创建一个表,但这不是此答案的重点),然后使用按位逻辑将它们连接在一起,然后分组为了获得每种类型的总数,请输入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_typesJOIN [Your amounts table] AS amounts_table ON amounts_table.Types & derived_types.type_bitmask = derived_types.type_bitmaskGROUP 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关于mysql - 基于按位运算条件的列的总和,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/59730974/
10-13 02:04