`near '(': 语法错误` 创建每个流派中畅销专辑的视图以及销售情况

发布于 2025-01-10 20:28:06 字数 749 浏览 0 评论 0原文

此代码使用 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

原文

This code uses the Chinook database to create a view called BestSeller for the bestselling album in each genre with sales (based on the quantity of tracks sold) with the columns Genre, Album, Artist, and 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;

I get the error

near '(': syntax error

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等数载，海棠开 2025-01-17 20:28:06

对于 3.25.0 之前的 SQLite 版本，您可以使用 2 级聚合并利用 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;

请参阅演示。

For versions of SQLite prior to 3.25.0, you can do it with 2 levels of aggregation and by utilizing SQLite's feature of bare columns:

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;

See the demo.

回复收藏 0 原文