我正在使用基本的 chinook 資料庫，但我不知道如何撰寫 SQLite 查詢來為花費最多的客戶（命名為 TopSpender）創建一個名為 topCustomers 的視圖（基于數量 * 單價，命名為 TotalSpending）每種型別的音樂都有銷量。列：型別、TopSpender、TotalSpending
專輯表的架構：
[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
客戶表：
[CustomerId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[FirstName] NVARCHAR(40) NOT NULL,
[LastName] NVARCHAR(20) NOT NULL,
[Company] NVARCHAR(80),
[Address] NVARCHAR(70),
[City] NVARCHAR(40),
[State] NVARCHAR(40),
[Country] NVARCHAR(40),
[PostalCode] NVARCHAR(10),
[Phone] NVARCHAR(24),
[Fax] NVARCHAR(24),
[Email] NVARCHAR(60) NOT NULL,
[SupportRepId] INTEGER,
FOREIGN KEY ([SupportRepId]) REFERENCES "employees" ([EmployeeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
發票表：
[InvoiceId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[CustomerId] INTEGER NOT NULL,
[InvoiceDate] DATETIME NOT NULL,
[BillingAddress] NVARCHAR(70),
[BillingCity] NVARCHAR(40),
[BillingState] NVARCHAR(40),
[BillingCountry] NVARCHAR(40),
[BillingPostalCode] NVARCHAR(10),
[Total] NUMERIC(10,2) NOT NULL,
FOREIGN KEY ([CustomerId]) REFERENCES "customers" ([CustomerId])
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 topCustomers AS
SELECT DISTINCT
g.Name Genre,
FIRST_VALUE(c.LastName || ' ' || c.FirstName) OVER (PARTITION BY g.GenreId ORDER BY SUM(ii.Quantity * ii.UnitPrice) DESC) TopSpender,
MAX(SUM(ii.Quantity * ii.UnitPrice)) OVER (PARTITION BY g.GenreId) TotalSpending
FROM genres g
INNER JOIN tracks t ON t.GenreId = g.GenreId
INNER JOIN invoice_items ii ON ii.TrackId = t.TrackId
INNER JOIN invoices i ON i.InvoiceId = ii.InvoiceId
INNER JOIN customers c ON c.CustomerId = i.CustomerId
GROUP BY g.GenreId, i.CustomerId;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/434080.html