MySQL，汇总多行并根据最多投票排名

Question

我有以下数据库，

id  rank1   rank2   rank3   rank4
1   5       4       8       9
2   5       8       9       4
3   8       5       3       1
4   5       8       2       1
5   8       5       3       1
6   5       8       3       1

我需要一个 mysql 查询或 php 脚本来统计排名并根据它在表中出现的次数显示前 4 个...即。最终结果应该类似于：

rank1 = 5
rank2 = 8
rank3 = 3
rank4 = 1

有什么想法吗？？？提前致谢

Answer 1

你的表设计远非最佳，如果你之前不认为是这样，那么在意识到获得你想要的结果的方法需要这个“不太漂亮”查询后，你肯定会看到它，虽然它有效。

SELECT name, rank FROM (
  (
    SELECT 'rank1' name, rank1 rank
    FROM foobar GROUP BY rank1
    ORDER BY count(*) DESC LIMIT 1
  ) rank1_foobar
)
UNION SELECT name, rank FROM (
  (
    SELECT 'rank2' name, rank2 rank
    FROM foobar GROUP BY rank2
    ORDER BY count(*) DESC LIMIT 1
  ) rank2_foobar
)
UNION SELECT name, rank FROM (
  (
    SELECT 'rank3' name, rank3 rank
    FROM foobar GROUP BY rank3
    ORDER BY count(*) DESC LIMIT 1
  ) rank3_foobar
)
UNION SELECT name, rank FROM (
  (
    SELECT 'rank4' name, rank4 rank
    FROM foobar GROUP BY rank4
    ORDER BY count(*) DESC LIMIT 1
  ) rank4_foobar
)

输出

+-------+------+
| name  | rank |
+-------+------+
| rank1 |    5 |
| rank2 |    8 |
| rank3 |    3 |
| rank4 |    1 |
+-------+------+

---

我会将您的表重组为如下所示的内容，这将使编写您所请求的查询变得更加容易。

CREATE TABLE ranks (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  group_id INT UNSIGNED NOT NULL COMMENT 'to be able to group more than one row in `ranks` together',
  rank_type ENUM('rank1','rank2','rank3','rank4'),
  rank_value INT,
  PRIMARY KEY(`id`)
);

Answer 2

由于数据标准化较差，它并不像单个 select/from group by 那么简单。您需要查询每个“Rank”列作为联合的一部分，然后将其汇总。为了降低临时临时总和，我们仍然可以对计数进行预分组，这样您就不会运行所有行 4 次，而是在相应的组段中每个等级预汇总 1 次，

select
      PreAgg.Rank,
      SUM( PreAgg.RankCount ) as TotalCount
   from 
      ( select
              YT.Rank1 as Rank,
              COUNT(*) as RankCount
           from 
              YourTable YT
           group by 
              YT.Rank1
        UNION ALL
        select
              YT.Rank2 as Rank,
              COUNT(*) as RankCount
           from 
              YourTable YT
           group by 
              YT.Rank2
        UNION ALL
        select
              YT.Rank3 as Rank,
              COUNT(*) as RankCount
           from 
              YourTable YT
           group by 
              YT.Rank3
        UNION ALL
        select
              YT.Rank4 as Rank,
              COUNT(*) as RankCount
           from 
              YourTable YT
           group by 
              YT.Rank4 ) PreAgg
   GROUP BY
      PreAgg.Rank,
      SUM( PreAgg.RankCount ) DESC

正如 Ajreal 所指出的，这将需要对结构进行更多澄清...是否有理由为什么您有 4 个不同的列，它们都是“排名”，而不是像这样的更规范化的表。

ID   RankGroup   Rank
1     1          5
2     1          5
3     1          8
4     1          5
5     1          8
6     1          5
7     2          4
7     2          8
7     2          5
7     2          8
7     2          5
7     2          8
etc for ranks 3 and 4

然后，无论“组级别如何”，您都可以获取每个排名的计数“ 健康）状况，或者在非常简化的查询中获得每组的最佳排名。