组按聚合函数计数行，映射计数到用户组

Question

对于那些熟悉Minecraft的人，我试图计算每个用户在单个SELECT查询中打破的每种类型的块的数量。我想这叫做GROUP BY聚合函数。我认为我需要使用多个聚合函数来实现我的最终目标。

到目前为止，我只能:

• 统计每种类型的块被打破的数量
• 统计每个用户破坏的区块总数

我认为我需要使用GROUP BY函数来计算每个块被打破的次数，然后在子查询中按每个用户分组。

---

问题伪代码:

玩家
• 选择, block_id , count (*) , action_type = "block-break"
• 计算一个区块被打破的次数(通过计算每个block_id的出现次数)
• GROUP BY block_id

我试过的:(输出)

SELECT player, block_id, COUNT(block_id) FROM prism_actions WHERE action_type = 'block-break' GROUP BY block_id;

。([输出])2

• 这个函数似乎在计算适当的值。

SELECT player, block_id, count(*) as count from prism_actions where action_type = 'block-break' group by block_id,player order by block_id;

---

表

(prism_actions)

Field Type id int(11) unsigned action_time timestamp action_type varchar(20) player varchar(16) world varchar(255) x int(11) y smallint(5) z int(11) block_id mediumint(5) block_subid mediumint(5) old_block_id mediumint(5) old_block_subid mediumint(5) data varchar(255)

---

我已经执行了一个类似的GROUP BY聚合查询来计数每个玩家#(死亡|杀死|方块破碎|方块放置)

• 这个查询工作如预期，但只需要一个单一的GROUP BY函数
• 基于action_type计算每个动作的SUM(即玩家-kill，玩家-death, block-break, block-place
)
• 输出如下:

lower(player) kills deaths blocks_broken blocks_placed joey 41 4 4911 2678 kate 74 1 4944 4762 bill 129 4 2782 1271 jack 335 12 12403 5886

代码: SELECT LOWER(player), SUM(CASE WHEN action_type = 'player-kill' THEN 1 ELSE 0 END) AS kills, SUM(CASE WHEN action_type = 'player-death' THEN 1 ELSE 0 END) AS deaths, SUM(CASE WHEN action_type = 'block-break' THEN 1 ELSE 0 END) AS blocks_broken, SUM(CASE WHEN action_type = 'block-place' THEN 1 ELSE 0 END) AS blocks_placed FROM prism_actions WHERE action_type IN ('block-break', 'block-place', 'player-kill', 'player-death') AND LOWER(player) NOT IN ('creeper', 'fireball', 'environment') GROUP BY player;

---

提前谢谢你。

我意识到我的代表在StackOverflow上很低，但我希望这不会影响你的答案，或导致任何形式的歧视。

Answer 1

在这里使用ROLLUP修饰符来获取所需的内容。

SELECT player, block_id, count(*) as count 
from prism_actions 
where action_type = 'block-break' 
group by player, block_id WITH ROLLUP
order by block_id;

这将给出一个类似于

的记录集

Player | Block | Count
NULL     NULL    100
1        NULL    100
1        1       75
1        2       25

其中player =1和block = NULL是总数，下面是按块类型计数。