sqlite `Near‘(’：语法错误`为每个流派的畅销专辑创建一个销量视图

gk7wooem 于 2022-11-15 发布在 SQLite

关注(0)|答案(2)|浏览(122)

这段代码使用Chinook数据库为每个流派中最畅销的专辑创建一个名为BESTOLER的视图，每个流派的销量(基于售出的曲目数量)包括Genre、Always、Artist和Sales列。

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

sqlite

来源：https://stackoverflow.com/questions/71315554/near-syntax-error-creating-a-view-for-bestselling-album-in-each-genre-wit

2条答案

按热度按时间

c86crjj01#

对于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;

请参阅demo。

赞(0）回复(0）举报 2022-11-15

mrfwxfqh2#

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

赞(0）回复(0）举报 2022-11-15