SQL - 按其他关系计数排序查询

Question

这是我的表格。

一首歌曲可以拥有更多的票数。 我想要获取所有歌曲，按照它们拥有的票数排序。 我该如何做到呢？

例如： 歌曲表格应该是这样的：

1,"Master Of Puppets"
2,"Don't Cry"
3,"Baby"
4,"Song name"
5,"I want to break free"

以及投票结果：

1,5
2,5
3,5
4,2
5,2
6,1

我想查询歌曲表以获取获得最多赞的歌曲，它们应该按照如下顺序排列。

5,"I want to break free"
2,"Don't cry"
1,"Master Of Puppets"
3,"Baby"
4,"Song name"

有没有一种方法可以只用一个查询完成这个任务？ 我知道我可以查询所有歌曲，然后针对每首歌曲查询投票表，其中歌曲ID是当前歌曲ID，但我能否只使用一个查询来完成这个任务？

- Boldijar Paul

---

有没有办法只用一个查询来完成这个任务？是的。使用外连接和对投票表进行计数。（假设您仍然希望获取所有歌曲，即使它没有得到投票） - xQbert

@xQbert 如果你使用 HAVING 子句，仍然可以在排除没有投票的歌曲的情况下完成它。 - EvilBob22

Answer 1

使用Count()聚合函数来计算每首歌曲获得的votes数量，并在Order by中使用它。

使用Left Outer Join可以返回song表中即使没有投票的歌曲行。

请尝试这样做。

SELECT s.id, 
       s.title 
FROM   song s 
       LEFT OUTER JOIN votes v 
                    ON s.id = v.songid 
GROUP  BY s.id, 
          s.title 
ORDER  BY Count(v.songid) DESC 

- Pரதீப்

---

@xQbert - 是的，最初忘记添加 :) - Pரதீப்

Answer 2

SELECT s.id, s.title FROM
(
  SELECT s.id, s.title, count(v.id) votes
  FROM 
   song s
  JOIN vote v on s.id = v.songId
   GROUP BY s.id, s.title
   ORDER BY votes desc 
);

可以是一个选项。

- Ivanka Eldé

---

如果我还有一个where子句怎么办？在我的情况下，它将是where playlistId = X，（一首歌曲也有一个播放列表ID），我应该使用playlist = X还是s.playlistId？ - Boldijar Paul

"s"只是一个别名，你也可以像"song.id"一样引用表"song"中的列。 这个别名可以帮助你区分具有相同名称但来自不同表的列（并缩短表名）。所以你的问题的答案是：当你确定在FROM或JOIN子句之后的其他表中没有“playlistId”列时，你可以使用“playlistId”，仅在“song”表中。我希望这很清楚。这里有例子. - Ivanka Eldé

在我的情况下，投票和歌曲都有一个playlistId。而且表名实际上是songs和votes。我想选择所有列而不仅仅是一些列。在这种情况下，我应该如何更改，并添加一个where子句来使song.playlistId = X？ - Boldijar Paul

在这种情况下，为避免出现“列模糊定义”的错误，您应该使用v.playlistId或s.playlistId。同时，如果您选择所有列，则可以用星号简写：“select * from ...”...SELECT * FROM ( SELECT s.id, s.title, s.playlistId, count(v.id) votes FROM songs s JOIN votes v on s.id = v.songId WHERE s.playlistId = 'X' GROUP BY s.id, s.title ORDER BY votes desc ); 大概就是这样。希望能对你有所帮助。 - Ivanka Eldé