Mysql select查询按通配符分组

Question

无法通过通配符对活跃论坛总结果进行分组。

最终，我希望将具有相似名称的所有结果分组并汇总到不同的行中。

表格:论坛

栏目:游戏、帖子

当前查询:

SELECT game, sum(posts) FROM forums WHERE posts>1 AND game like
'%rift%' OR game like '%swiftsure%' GROUP BY name

当前结果:

Game posts

Rift (PVP Defiant): Briarcliff 3406

Rift Guides 100

Rift Leadership 1541

Rift Raid Discussion 1336

Rift Raid Leadership 533

Rift: Cleric 85

Rift: Mage 87

Rift: Private 411

Rift: PVE Leadership 113

Rift: Rogue 56

Rift: Warrior 29

Star Wars The Old Republic PvP - Swiftsure 7

SWTOR Swiftsure Leadership 0

SWTOR: Swiftsure Private 7

期望的结果如下所示:

Game | posts

Rift | 7697

Swiftsure | 14

我当然感谢任何和所有的帮助!

Answer 1

由于您正在寻找通用分组，因此我会做一个 CASE/WHEN 作为列...

SELECT case when game like '%rift%'      then 'Rift   '
            when game like '%swiftsure%' then 'Swiftsure'
       end as GroupName,
       count(*) as  PostCount
   from
      forums
   where
         game like '%rift%' 
      OR game like '%swiftsure%' 
   GROUP BY
      GroupName