sql - 选择艺术家专辑列表并显示每个专辑中已请求艺术家和专辑的轨道数

Question

我需要进行复杂的查询。我有 table :

CREATE TABLE IF NOT EXISTS tracks
     ( id INTEGER PRIMARY KEY, path TEXT,
     title TEXT, artist TEXT,
     album TEXT, genre TEXT, 
    duration INTEGER, rating INTEGER );

抱歉，问题的标题很脏，但我不明白如何更简短地解释它。

问题:如何显示所请求艺术家的专辑列表，并为每个显示的专辑计算单个查询中当前专辑中已请求艺术家的轨道数。

我尝试过这样的事情:

SELECT albtbl.album as album, albtbl.artist as artist, 
( SELECT count(*) FROM trackstbl WHERE trackstbl.artist = albtbl.aritst ) as tracksCount 
FROM ( SELECT tbl.album as album, tbl.artist as artist, count(title) as tracksCount FROM tracks as tbl  WHERE tbl.artist = 'requested_artist'
 GROUP BY tbl.album ) as albtbl, FROM tracks as trackstbl ORDER BY album ASC

但未编译:

SQLiteException: near "FROM": syntax error:

Answer 1

不确定为什么这很复杂。以下查询应返回搜索艺术家的每个专辑的轨道数:

     select artist, album, count(*) as tracksCount
     from tracks
     where artist = 'requested_artist'
     group by artist, album;