mysql - 按同一表的多个列的计数排序

Question

因此，我在帖子表中添加了以下列:team_id、player_id、league_id、 coach_id、venue_id。任何给定的帖子的每一列上都可以有一堆 id。

现在，我想按任何 id 的 DESC 计数进行排序。例如:结果应该是:
3 - league_id//因为它的总数为3
7 - team_id//因为它的总数为 2
8 - player_id//因为它的计数为 2 等等。

基本上，我们按计数 DESC 排序，但它必须来自相同的列数。
有什么办法我可以得到它吗？它还必须给我列名称，以便我知道该列的 id。谢谢!

Answer 1

正确的数据模型将为每个关系都有一个单独的联结/关联表:

• post_teams
• post_players
• post_leagues
• post_coaches
• post_venues

其中每一个都会正确声明与两个引用表的外键关系。

有了这样的模型，您的查询实际上会非常简单:

select which, id, count(*)
from ((select post_id, team_id as id, 'team' as which
       from post_teams
      ) union all
      (select post_id, players_id as id, 'players' as which
       from post_players
      ) union all
      (select post_id, leagues_id as id, 'leagues' as which
       from post_leagues
      ) union all
      (select post_id, coach_id as id, 'coach' as which
       from post_coaches
      ) union all
      (select post_id, venue_id as id, 'venue' as which
       from post_venues
      )
     ) x
group by which, id
order by count(*) desc;

我建议您修复数据模型，或者切换到对数组具有 native 支持的数据库。 MySQL 有一个很好的存储列表的方法。它被称为表，而不是字符串。