sql - 加入计数

Question

我想找出哪个播放列表有超过 2 首歌曲。该声明有效，但我想要播放列表的名称和显示歌曲的 count() 。我想我必须使用连接，但我不明白它应该如何工作。有人可以帮忙吗？

playlist table
++++++++++++++
id
name

playlist_songs table
++++++++++++++++++++
song_id
playlist_id


SELECT p.name FROM playlist p 
WHERE p.id in (SELECT s.playlist_id counter FROM playlist_songs s
group by playlist_id
having count(song_id)>2);

Answer 1

I want the name of the playlist and the count() for the songs displayed.

这是使用 JOIN 而不是 IN 谓词的优点之一:

SELECT 
  p.name, 
  COUNT(song_id) counter
FROM playlist p 
INNER JOIN playlist_songs s ON  p.id = s.playlist_id 
GROUP BY playlist_id
HAVING COUNT(song_id) > 2;