我正在使用基本的 chinook 資料庫，但我不知道如何撰寫一個 SQLite 查詢來創建一個名為 BestSeller 的視圖，用于每個流派中的暢銷專輯（基于銷售的曲目數量，命名為 Sales）列：流派、專輯、藝術家、銷售。
專輯表的架構：
[Title] NVARCHAR(160) NOT NULL,
[ArtistId] INTEGER NOT NULL,
FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
藝術家表：
[ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)
跟蹤表架構：
[TrackId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(200) NOT NULL,
[AlbumId] INTEGER,
[MediaTypeId] INTEGER NOT NULL,
[GenreId] INTEGER,
[Composer] NVARCHAR(220),
[Milliseconds] INTEGER NOT NULL,
[Bytes] INTEGER,
[UnitPrice] NUMERIC(10,2) NOT NULL,
FOREIGN KEY ([AlbumId]) REFERENCES "albums" ([AlbumId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([GenreId]) REFERENCES "genres" ([GenreId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([MediaTypeId]) REFERENCES "media_types" ([MediaTypeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
表發票專案：
[InvoiceLineId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[InvoiceId] INTEGER NOT NULL,
[TrackId] INTEGER NOT NULL,
[UnitPrice] NUMERIC(10,2) NOT NULL,
[Quantity] INTEGER NOT NULL,
FOREIGN KEY ([InvoiceId]) REFERENCES "invoices" ([InvoiceId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([TrackId]) REFERENCES "tracks" ([TrackId])
ON DELETE NO ACTION ON UPDATE NO ACTION
我也有所有這些其他表。
albums employees invoices playlists
artists genres media_types tracks
customers invoice_items playlist_track
任何幫助表示贊賞。
uj5u.com熱心網友回復：
加入相關表格并按流派和專輯分組，以獲取每張專輯的銷售曲目數量。
然后使用FIRST_VALUE()和MAX()視窗函式得到每個流派銷量最高的專輯和銷量：
CREATE VIEW BestSeller 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;
查看演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/434081.html