c# - 使用 group by 获取结果

Question

我的数据库中有两个表:

song   => id, name, song_ref, views 
artist => id, name

我想从歌曲表中获取所有艺术家，每个艺术家的歌曲观看次数最多。
我尝试使用 MAX(views) 来获取最大观看次数值，但没有成功获取这首相关歌曲的其他字段。

SELECT a.name, MAX(s.views) FROM song s inner join artist a 
ON s.artist_ref = a.idArtist GROUP BY a.name

有什么想法吗？谢谢你

Answer 1

试试这个:

SELECT *
FROM
  (SELECT a.id AS aid,
          a.name AS artist,
          s.id AS sid,
          s.name AS most_viewed,
          s.views
   FROM artist a
   INNER JOIN song s ON s.artist_ref=a.id
   ORDER BY a.id,
            s.views DESC) c
GROUP BY c.aid;