来自两个查询的MySQL和列

Question

方琴键

我正在开发一个装甲建造应用程序，在这个应用程序中，一个角色可以同时拥有盔甲和魅力的技能，我正在努力总结他们，这样用户就可以知道他们选择的物品的总数。我有一个与盔甲相加的查询和一个与魅力相加的查询，但我需要一种组合它们的方法。以下是我到目前为止所拥有的：

获取装甲技能的查询：

SELECT skills.id AS skillId, skills.name as skillName, SUM(armor_skills.ranks) as ranks from skills
INNER JOIN armor_skills ON armor_skills.skill_id = skills.id
WHERE armor_skills.armor_id in (457,458,459,460,461)
GROUP BY skillId, skillName;

返回：

7  | Skill One    | 3
39 | Skill Two    | 5
63 | Skill Three  | 1
71 | Skill Four   | 1
76 | Skill Five   | 3

然后，我有一个查询来获得魅力技能：

SELECT skills.id AS skillId, skills.name AS skillName, SUM(skill_ranks.rank) AS ranks FROM charm_skills
INNER JOIN charm_ranks ON charm_skills.charm_rank_id = charm_ranks.id
INNER JOIN skills ON charm_skills.skill_id = skills.id
INNER JOIN skill_ranks ON skills.id = skill_ranks.skill_id AND charm_ranks.rank = skill_ranks.rank
WHERE charm_ranks.id = 57
GROUP BY skillId, skillName;

返回：

39 | Skill Two | 3

我可以将这两项查询合并如下：

(SELECT skills.id AS skillId, skills.name AS skillName, SUM(armor_skills.ranks) AS ranks FROM skills
INNER JOIN armor_skills ON armor_skills.skill_id = skills.id
WHERE armor_skills.armor_id IN (457,458,459,460,461)
GROUP BY skillId, skillName)
UNION
(SELECT skills.id AS skillId, skills.name AS skillName, SUM(skill_ranks.rank) AS ranks FROM charm_skills
INNER JOIN charm_ranks ON charm_skills.charm_rank_id = charm_ranks.id
INNER JOIN skills ON charm_skills.skill_id = skills.id
INNER JOIN skill_ranks ON skills.id = skill_ranks.skill_id AND charm_ranks.rank = skill_ranks.rank
WHERE charm_ranks.id = 57
GROUP BY skillId, skillName);

返回：

7  | Skill One    | 3
39 | Skill Two    | 5
63 | Skill Three  | 1
71 | Skill Four   | 1
76 | Skill Five   | 3
39 | Skill Two    | 3

但我试着得到：

7  | Skill One    | 3
39 | Skill Two    | 8
63 | Skill Three  | 1
71 | Skill Four   | 1
76 | Skill Five   | 3

Answer 1

我认为你的问题类似于使用联合mysql选择查询组这篇文章。在您的联合(使用union all)周围使用一个选择包装器，如文章最后的一个组所看到的那样。我没有测试它，但它应该类似于以下内容。

SELECT skillId, skillName, SUM(ranks) from 
((SELECT skills.id AS skillId, skills.name AS skillName, 
SUM(armor_skills.ranks) AS ranks FROM skills INNER JOIN armor_skills ON 
armor_skills.skill_id = skills.id WHERE armor_skills.armor_id IN 
(457,458,459,460,461) GROUP BY skillId, skillName) 
UNION ALL 
(SELECT skills.id AS skillId, skills.name AS skillName, SUM(skill_ranks.rank) AS 
ranks FROM charm_skills INNER JOIN charm_ranks ON charm_skills.charm_rank_id 
= charm_ranks.id INNER JOIN skills ON charm_skills.skill_id = skills.id 
INNER JOIN skill_ranks ON skills.id = skill_ranks.skill_id AND 
charm_ranks.rank = skill_ranks.rank WHERE charm_ranks.id = 57 GROUP BY 
skillId, skillName)) t 
GROUP BY skillId,skillName;