我有一张桌子。

ID | Winner   | Loser    | WinningCaster | LosingCaster
0  | Player A | Player B | Warcaster A   | Warcaster B
1  | Player A | Player B | Warcaster C   | Warcaster A
2  | Player C | Player D | Warcaster A   | Warcaster B


等等..

具有Player和Warcaster的各种值。
WinningCaster / LosingCaster是一个有限的名称列表,我想进行一个查询,该查询将在有和没有特定玩家条目的情况下跨两列查找最常出现的名称。
IE Player A应该返回带有2的WarcasterA,而整体查询应该返回带有3的WarcasterA。

到目前为止,我只能从以下任一列中获得最频繁的访问,而不能从这两列中获得最频繁的访问;

SELECT
    ID, Winner, Loser, CasterWinner, Count(CasterWinner) AS Occ
FROM
    `Games`
GROUP BY
    CasterWinner
ORDER BY
    Occ DESC
LIMIT 1

最佳答案

使用union all

select caster, count(*)
from ((select casterwinner as caster from games
      ) union all
      (select casterloser from games
      )
     ) c
group by caster
order by count(*) desc
limit 1;

关于mysql - 从多列中选择最频繁,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/40246363/

10-12 03:24