组合两个单独工作的 sqlite 查询

Question

我正在研究基本的 chinook 数据库，并尝试编写一个 sqlite 查询来为 10 位畅销艺术家创建一个名为 v10BestSellingArtists 的视图，该视图基于按 TotalTrackSales 降序排列的已售曲目总数（名为 TotalTrackSales）。 TotalAlbum 是每位艺术家售出的包含曲目的专辑数量。

我可以分别为它们两个查询编写查询，但我不知道如何合并这两个查询：

用于查找 Totaltracksales 的查询：

Select 
r.name as artist, 
count (i.quantity) as TotalTrackSales 
from albums a 
left join tracks t on t.albumid == a.albumid 
left join invoice_items i on i.trackid == t.trackid 
left join artists r on a.artistid == r.artistid 
group by r.artistid 
order by 2 desc 
limit 10;

以及用于 TotalAlbum 的第二个查询：

Select 
r.name as artist, 
count(a.artistId) from albums a 
left join artists r where a.artistid == r.artistid 
group by a.artistid 
order by 2 desc 
limit 10;

但我想要一个包含以下列的查询：Artist、TotalAlbum TotalTrackSales。

任何帮助表示赞赏。

专辑表的架构：

 [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

表invoice_items：

    [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

Answer 1

为了合并您的 2 个查询，您可以使用 CTE 执行以下操作：

with total_track_sales as (
Select 
r.name as artist, 
count (i.quantity) as TotalTrackSales 
from albums a 
left join tracks t on t.albumid == a.albumid 
left join invoice_items i on i.trackid == t.trackid 
left join artists r on a.artistid == r.artistid 
group by r.artistid 
order by 2 desc 
limit 10 ),
with total_album as (
Select 
r.name as artist, 
count(a.artistId) as TotalAlbums from albums a 
left join artists r where a.artistid == r.artistid 
group by a.artistid 
order by 2 desc 
limit 10 )

select artist, TotalTrackSales, TotalAlbums
from total_track_sales ts inner join total_album ta
on ts.artist = ta.artist

Answer 2

您可以尝试使用 DISTINCT 并组合聚合和窗口函数进行单个查询。

select * 
from (
   select 
      r.name as artist, 
      count (i.quantity) as TotalTrackSales,
      row_number() over (order by count (i.quantity) desc) rnT,
      count (distinct a.albumid) as totalAlbums,
      row_number() over (order by count (distinct a.albumid) desc) rnA,
   from albums a 
   left join tracks t on t.albumid == a.albumid 
   left join invoice_items i on i.trackid == t.trackid 
   left join artists r on a.artistid == r.artistid 
   group by r.artistid 
)
where rnT <= 10 or rnA <= 10