问题
我知道如何对不同的表做多个计数,但从没见过如何将他们合起来。我有一个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
欢迎来弦圈一起翻译StackOverflow等国外编程内容👇👇👇
翻译原文:将多个表的计数合为一个计数
更多数据库相关翻译内容:数据库 - 弦圈
更多其他趣味内容:弦圈 - 找到属于你的圈子