sqlite 选择艺术家的专辑列表和显示每个专辑曲目计数已请求的艺术家和专辑列表

bqujaahr 于 2023-05-07 发布在 SQLite

关注(0)|答案(4)|浏览(215)

我需要做一个复杂的查询。我有一张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：靠近“FROM”：语法错误：

sqlite

来源：https://stackoverflow.com/questions/13664089/select-list-of-albums-of-artist-and-display-for-every-album-tracks-count-which-h

4条答案

按热度按时间

cgvd09ve1#

不知道为什么这很复杂。下面的查询应该返回您的轨道计数为每个专辑的搜索艺术家：

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

赞(0）回复(0）举报 2023-05-07

0lvr5msh2#

select artist, album, count(1) as countTracks
from tracks
where artist = @requestedArtist
group by artist, album

赞(0）回复(0）举报 2023-05-07

j2cgzkjk3#

它并不复杂，但它很难阅读。我一眼就看出了错误。这里：

as albtbl, FROM tracks

逗号是额外的。把它改成这样：

as albtbl FROM tracks

希望能帮上忙。

赞(0）回复(0）举报 2023-05-07

icomxhvb4#

我们可以通过子查询的帮助来实现这一点，下面是示例：

SELECT DISTINCT album, artist,
    (SELECT COUNT(*) 
     FROM tracks AS Sub_Track
     WHERE Sub_Track.album = Main_Track.album AND Sub_Track.artist = Main_Track.artist) AS Track_Count
FROM tracks AS Main_Track
WHERE artist = 'requested_artist'
ORDER BY album ASC;

上面的查询为所请求的艺术家选择不同的专辑和艺术家对，然后它使用子查询来获得每个专辑和艺术家组合的曲目数量的计数。

赞(0）回复(0）举报 2023-05-07

我来回答

sqlite 选择艺术家的专辑列表和显示每个专辑曲目计数已请求的艺术家和专辑列表

4条答案

相关问题

热门标签

最新问答