mysql - 如何统计同一个表不同列中的外键？

Question

我正在开发这个系统，我有一张 table 供球队使用，一张 table 供 super 明星使用。在球队表中，我有这 4 列，它们具有引用 super 明星表的外键。

Table Teams Picture

我的目标是知道谁是球队中被选中最多的 super 巨星。目前我正在使用 4 个代码并手动对结果求和。

我正在使用的代码:

SELECT superstars.name,COUNT(superstar01) FROM smackdown_teams JOIN superstars on smackdown_teams.superstar01 = superstars.id GROUP by superstar01 Order by COUNT(superstar01) desc;
SELECT superstars.name,COUNT(superstar02) FROM smackdown_teams JOIN superstars on smackdown_teams.superstar02 = superstars.id GROUP by superstar02 Order by COUNT(superstar02) desc;
SELECT superstars.name,COUNT(superstar03) FROM smackdown_teams JOIN superstars on smackdown_teams.superstar03 = superstars.id GROUP by superstar03 Order by COUNT(superstar03) desc;
SELECT superstars.name,COUNT(superstar04) FROM smackdown_teams JOIN superstars on smackdown_teams.superstar04 = superstars.id GROUP by superstar04 Order by COUNT(superstar04) desc;

有什么建议可以告诉我如何知道谁是被选中最多的 ID 吗？

Answer 1

您可以合并结果并找到结果的最大值。

select max(superstar)
(SELECT superstars.name,COUNT(superstar01) as superstar FROM smackdown_teams JOIN superstars on smackdown_teams.superstar01 = superstars.id GROUP by superstar01 Order by COUNT(superstar01) desc
UNION
SELECT superstars.name,COUNT(superstar02) as superstar FROM smackdown_teams JOIN superstars on smackdown_teams.superstar02 = superstars.id GROUP by superstar02 Order by COUNT(superstar02) desc)