将多个表的计数合为一个计数-CSDN博客

问题

我知道如何对不同的表做多个计数，但从没见过如何将他们合起来。我有一个MySQL数据库，在那里我执行以下查询：

SELECT characters.name, COUNT(*) AS wiki_unlocks
    FROM wiki_items
    INNER JOIN characters
    ON characters.character_id=wiki_items.character_id
    GROUP BY wiki_items.character_id
    ORDER BY wiki_unlocks DESC
    LIMIT 10;

这给我返回了以下结果，看着还不错：

name          wiki_unlocks
player1       2
player2       1

我想要得到一个所有’wiki_xxxx’表的加在一起的计数。比如说，我想要得到’wiki_items’(above) + ‘wiki_armors’ + ‘wiki_weapons’ + …

感谢大家的帮助！

3个回答

如果性能方面是一个问题，毕竟表中有很多行数据，我会这么做。首先分组和计数，然后才是连接表。

SELECT characters.name, 
COALESCE(count_unlocks,0) AS unlocks, 
COALESCE(count_armors,0) AS armors,
COALESCE(count_weapons,0) AS weapons,
COALESCE(count_unlocks,0) + COALESCE(count_armors,0) + COALESCE(count_weapons,0) AS total
FROM characters
LEFT JOIN 
(SELECT wiki_items.character_id, count(*) AS count_unlocks from wiki_items
GROUP BY wiki_items.character_id) AS wiki_unlocks
ON characters.character_id = wiki_unlocks.character_id
LEFT JOIN
(SELECT wiki_armors.character_id, count(*) AS count_armors from wiki_armors
GROUP BY wiki_armors.character_id) AS wiki_armors
ON characters.character_id = wiki_armors.character_id
LEFT JOIN
(SELECT wiki_weapons.character_id, count(*) AS count_weapons from wiki_weapons
GROUP BY wiki_weapons.character_id) AS wiki_weapons
ON characters.character_id = wiki_weapons.character_id

可能最简单的方法是将每个计数作为子选择：

SELECT c.name
     , (select COUNT(i.character_id) 
         From wiki_items i
         Where   c.character_id=i.character_id

       ) as  wiki_unlocks
     , (select COUNT(a.character_id) 
         From wiki_armors a
         Where   c.character_id=a.character_id

       ) as  wiki_armors
     , (select COUNT(w.character_id) 
         From wiki_weapons w
         Where   c.character_id=w.character_id

       ) as  wiki_weapons  

FROM characters c

这或许会有帮助：

SELECT
    Sum( a.count )
FROM(
    SELECT Count( * ) AS count FROM Table1
    UNION ALL
    SELECT Count( * ) AS count FROM Table2
    UNION ALL
    SELECT Count( * ) AS count FROM Table3
    UNION ALL
    SELECT Count( * ) AS count FROM Table4
) a