如何创建视图按Poll分组统计各投票中歌曲的得票次数？

数据库存储用户在多个投票活动（Poll）中投票的歌曲，部分歌曲可属于多个Poll。简化后的示例表如下：

+------+------------------------------------+
| POLL | SONG                               | 
+------+------------------------------------+
|  1   | ABBA - I HAVE A DREAM              |
|  1   | 10 CC - DREADLOCK HOLIDAY          |
|  1   | ABBA - I HAVE A DREAM              |
|  1   | ABBA - I HAVE A DREAM              |
|  1   | ANDREA BERG - WENN DU MICH WILLST  |
|  2   | QUEEN - WE WILL ROCK YOU           | 
|  2   | ABBA - I HAVE A DREAM              |
|  2   | QUEEN - WE WILL ROCK YOU           | 
|  2   | HELEN SHAPIRO - QUEEN FOR TONIGHT  |
|  3   | ABBA - I HAVE A DREAM              |
+------+------------------------------------+

现有SQL脚本创建的视图是跨所有Poll统计歌曲总得票数，未按Poll区分：

CREATE OR REPLACE VIEW `voteResults` AS
SELECT
  poll,
  song,
  COUNT(song) AS count
FROM votes
GROUP BY song
ORDER BY count DESC

需要实现按每个Poll分别统计其中歌曲的得票数，视图输出需如下：

+------+------------------------------------+-------+
| POLL | SONG                               | COUNT |
+------+------------------------------------+-------+
|  1   | ABBA - I HAVE A DREAM              |   3   |
|  1   | 10 CC - DREADLOCK HOLIDAY          |   1   |
|  1   | ANDREA BERG - WENN DU MICH WILLST  |   1   |
|  2   | QUEEN - WE WILL ROCK YOU           |   2   |
|  2   | ABBA - I HAVE A DREAM              |   1   |
|  2   | HELEN SHAPIRO - QUEEN FOR TONIGHT  |   1   |
|  3   | ABBA - I HAVE A DREAM              |   1   |
+------+------------------------------------+-------+

核心问题是原SQL的GROUP BY仅按song分组，导致统计的是歌曲在所有Poll中的总票数，而非单个Poll内的票数。调整后的最优SQL如下：

CREATE OR REPLACE VIEW `voteResults` AS
SELECT
  poll,
  song,
  COUNT(*) AS count
FROM votes
GROUP BY poll, song
ORDER BY poll ASC, count DESC;

关键调整说明： #

• GROUP BY 子句：同时按poll和song分组，确保统计的是每首歌在单个Poll内的得票数，而非跨Poll的总和。
• COUNT函数：用COUNT(*)替代COUNT(song)更简洁，只要song字段不为空，两者效果完全一致。
• ORDER BY 子句：先按poll升序排列，保证同一Poll的结果集中展示；再按count降序排列，让每个Poll内得票更高的歌曲排在前面，完全匹配需求的输出格式。

内容的提问来源于stack exchange，提问作者NorthFred