问题

以下是翻译的内容:

原始数据库的简化版本如下表所示。该数据库包含了用户投票的各个调查中的歌曲。有多个可用的调查，有些歌曲可能属于多个调查。

简化示例：

+------+------------------------------------+
| 调查 | 歌曲                                | 
+------+------------------------------------+
|  1   | ABBA - 我有一个梦想              |
|  1   | 10 CC - 辫子假期                   |
|  1   | ABBA - 我有一个梦想              |
|  1   | ABBA - 我有一个梦想              |
|  1   | ANDREA BERG - 如果你愿意的话      |
|  2   | QUEEN - 我们会摇滚                | 
|  2   | ABBA - 我有一个梦想              |
|  2   | QUEEN - 我们会摇滚                | 
|  2   | HELEN SHAPIRO - 今晚的女王        |
|  3   | ABBA - 我有一个梦想              |
+------+------------------------------------+

下面的SQL脚本创建了一个跨越所有调查的视图，并计算了用户投票的所有歌曲的数量（不考虑调查）。

CREATE OR REPLACE VIEW `voteResults` AS
SELECT
  调查,
  歌曲,
  COUNT(歌曲) AS 数量
FROM 投票
GROUP BY 歌曲
ORDER BY 数量 DESC

但是，我需要找到一种方法来计算属于每个调查的歌曲数量，因此视图应该具有以下输出：

+------+------------------------------------+-------+
| 调查 | 歌曲                                | 数量  |
+------+------------------------------------+-------+
|  1   | ABBA - 我有一个梦想              |   3   |
|  1   | 10 CC - 辫子假期                   |   1   |
|  1   | ANDREA BERG - 如果你愿意的话      |   1   |
|  2   | QUEEN - 我们会摇滚                |   2   |
|  2   | ABBA - 我有一个梦想              |   1   |
|  2   | HELEN SHAPIRO - 今晚的女王        |   1   |
|  3   | ABBA - 我有一个梦想              |   1   |
+------+------------------------------------+-------+

如何最好地实现这一目标？

+------+------------------------------------+
| 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              |
+------+------------------------------------+

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

+------+------------------------------------+-------+
| 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   |
+------+------------------------------------+-------+

答案1

得分: 1

使用 GROUP BY (poll, song) 而不是 GROUP BY song 将给你想要的结果。

根据你的示例输出，你可能还想按 (poll, count desc) 进行排序，而不是仅使用 (count desc)。

这是 查询：

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

<details>
<summary>英文:</summary>

Use group by (poll, song) instead of group by song will give you the result you want.

Based on your example output, you probably also want to sort by (poll, count desc), instead (count desc) alone.

Here is the [query](http://sqlfiddle.com/#!18/6de9a/1):

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

|poll 	|song 	|count|
|-|-|-|
|1 	|ABBA - I HAVE A DREAM| 	3|
|1 	|ANDREA BERG - WENN DU MICH WILLST| 	1|
|1 	|10 CC - DREAMLOCK HOLIDAY| 	1|
|2 	|QUEEN - WE WILL ROCK YOU| 	2|
|2 	|HELEN SHAPIRO - QUEEN FOR TONIGHT| 	1|
|2 	|ABBA - I HAVE A DREAM| 	1|
|3 |ABBA - I HAVE A DREAM| 	1|


</details>