"near"(：语法错误"创建每种流派的畅销专辑的视图，销量

Question

此代码使用Chinook数据库为每种流派的畅销专辑创建一个名为BestSeller的视图，其中包含流派、专辑、艺术家和销售额列的销售额(基于售出的曲目数量(。

CREATE VIEW vBestSellingGenreAlbum AS SELECT DISTINCT 
g.Name Genre, 
FIRST_VALUE(a.Title) OVER (PARTITION BY g.GenreId ORDER BY COUNT(*) DESC) Album, 
FIRST_VALUE(r.Name) OVER (PARTITION BY g.GenreId ORDER BY COUNT(*) DESC) Artist, 
MAX(COUNT(*)) OVER (PARTITION BY g.GenreId) Sales
FROM genres g
INNER JOIN tracks t ON t.GenreId = g.GenreId
INNER JOIN albums a ON a.AlbumId = t.AlbumId
INNER JOIN artists r ON r.ArtistId = a.ArtistId
INNER JOIN invoice_items i ON i.TrackId = t.TrackId
GROUP BY g.GenreId, a.AlbumId;
SELECT * FROM vBestSellingGenreAlbum;

我得到错误

near '(': syntax error

Answer 1

对于3.25.0之前的SQLite版本，您可以使用两个聚合级别，并利用SQLite的裸列功能：

SELECT Genre, Album, Artist, MAX(Sales) Sales
FROM (
SELECT g.Name Genre, 
a.Title Album, 
r.Name Artist, 
COUNT(*) Sales
FROM genres g
INNER JOIN tracks t ON t.GenreId = g.GenreId
INNER JOIN albums a ON a.AlbumId = t.AlbumId
INNER JOIN artists r ON r.ArtistId = a.ArtistId
INNER JOIN invoice_items i ON i.TrackId = t.TrackId
GROUP BY g.GenreId, a.AlbumId
)
GROUP BY Genre;

请参阅演示

Answer 2

如果您使用的是Linux，请检查LD_LIBRARY_PATH中的sqlite lib>3.25。您的系统中可能安装了多个版本的sqlite。确保最新版本(>3.25(在LD_LIBRARY_PATH中具有优先权。