问题
我知道如何对不同的表做多个计数，但从没见过如何将他们合起来。我有一个MySQL数据库，在那里我执行以下查询：
SELECT characters.name, COUNT(*) AS wiki_unlocksFROM wiki_itemsINNER JOIN charactersON characters.character_id=wiki_items.character_idGROUP BY wiki_items.character_idORDER BY wiki_unlocks DESCLIMIT 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 iWhere c.character_id=i.character_id) as wiki_unlocks, (select COUNT(a.character_id) From wiki_armors aWhere c.character_id=a.character_id) as wiki_armors, (select COUNT(w.character_id) From wiki_weapons wWhere c.character_id=w.character_id) as wiki_weapons FROM characters c
- 这或许会有帮助：
SELECTSum( a.count )
FROM(SELECT Count( * ) AS count FROM Table1UNION ALLSELECT Count( * ) AS count FROM Table2UNION ALLSELECT Count( * ) AS count FROM Table3UNION ALLSELECT Count( * ) AS count FROM Table4
) a
欢迎来弦圈一起翻译StackOverflow等国外编程内容👇👇👇
翻译原文：将多个表的计数合为一个计数
更多数据库相关翻译内容：数据库 - 弦圈
更多其他趣味内容：弦圈 - 找到属于你的圈子