如何用MySQL实现带排名的多选投票系统的选项排名次数统计

项目背景与需求 #

• 为Web项目设计数据库，收集用户对1000个艺术收藏的情感反馈
• 用户从对应收藏的12个情感选项中选3个，并排名为第1、第2、第3名
• 需要生成统计报表：按collection_id和option_id分组，统计每个选项作为第1、第2、第3名的次数

现有数据库表结构 #

1. collection_poll表（存储艺术收藏信息） #

+-------+--------------------+  
|   id  |   collection_name  |   
+-------+--------------------+
|   1   |    collection 1    | 
|   2   |    collection 2    | 
|  ...  |        ...         |
+-------+--------------------+

2. option表（存储每个收藏对应的情感选项） #

+--------------------+--------------------+----------------+
|   collection_id    |      option_id     |      Text      |
+--------------------+--------------------+----------------+
|         1          |          1         |    Emotion 1   |
|         1          |          2         |    Emotion 2   |
|        ...         |         ...        |       ...      |
+--------------------+--------------------+----------------|

3. vote表（记录用户投票数据） #

+--+-------+-------------+-------------+-------------+-------------+
|id|user_id|collection_id|1st_option_id|2nd_option_id|3rd_option_id|
+--+-------+-------------+-------------+-------------+-------------+
|1 |   1   |      1      |      1      |      8      |      12     |
|2 |   2   |      1      |      3      |      1      |      8      |
|  ...     ...         ...         ...         ...         ...    |
+--+-------+-------------+-------------+-------------+-------------+

当前进展与问题 #

已创建视图统计第1名的次数，但无法将三个排名的统计结果整合到同一张表中：

CREATE OR REPLACE VIEW poll_results_first_option_count AS
SELECT
  vote.collection_id,
  vote.first_option_id,
  count(*) AS first_count
FROM
  vote
GROUP BY
  vote.collection_id,
  vote.first_option_id
ORDER BY
  vote.collection_id,
  vote.first_option_id;

解决方案 #

方法1：条件聚合（推荐） #

直接通过CASE WHEN配合聚合函数，一次扫描表完成所有统计，同时关联option表确保所有选项（包括得票为0的）都被纳入报表：

SELECT
  o.collection_id,
  o.option_id,
  COUNT(CASE WHEN v.1st_option_id = o.option_id THEN 1 END) AS 1st_count,
  COUNT(CASE WHEN v.2nd_option_id = o.option_id THEN 1 END) AS 2nd_count,
  COUNT(CASE WHEN v.3rd_option_id = o.option_id THEN 1 END) AS 3rd_count
FROM
  option o
LEFT JOIN vote v ON o.collection_id = v.collection_id
GROUP BY
  o.collection_id,
  o.option_id
ORDER BY
  o.collection_id,
  o.option_id;

方法2：关联多视图整合 #

如果需要保留现有统计视图，可以先创建另外两个排名的统计视图，再通过左连接整合所有数据：

-- 创建第2名统计视图
CREATE OR REPLACE VIEW poll_results_second_option_count AS
SELECT
  collection_id,
  2nd_option_id AS option_id,
  COUNT(*) AS 2nd_count
FROM vote
GROUP BY collection_id, 2nd_option_id;

-- 创建第3名统计视图
CREATE OR REPLACE VIEW poll_results_third_option_count AS
SELECT
  collection_id,
  3rd_option_id AS option_id,
  COUNT(*) AS 3rd_count
FROM vote
GROUP BY collection_id, 3rd_option_id;

-- 整合所有数据
SELECT
  o.collection_id,
  o.option_id,
  COALESCE(f.first_count, 0) AS 1st_count,
  COALESCE(s.2nd_count, 0) AS 2nd_count,
  COALESCE(t.3rd_count, 0) AS 3rd_count
FROM option o
LEFT JOIN poll_results_first_option_count f 
  ON o.collection_id = f.collection_id AND o.option_id = f.first_option_id
LEFT JOIN poll_results_second_option_count s 
  ON o.collection_id = s.collection_id AND o.option_id = s.option_id
LEFT JOIN poll_results_third_option_count t 
  ON o.collection_id = t.collection_id AND o.option_id = t.option_id
ORDER BY o.collection_id, o.option_id;

说明 #

• 两种方法都会输出所有选项的统计结果，包括得票次数为0的条目，完全匹配需求报表格式
• 条件聚合方法性能更优，只需一次表扫描；视图关联方式更直观，适合需要单独调用各排名统计的场景
• 使用COALESCE函数将左连接产生的NULL值转换为0，保证报表展示一致性

内容的提问来源于stack exchange，提问作者黃紹齊