SQLite3从两个不同的表中选择

Question

我想创建一个使用SQLite3来选择一个专辑名称的新表，然后从另一个表中获取所有具有albumID外键的曲目并对它们进行计数。是否有捷径可寻？由于

 CREATE TABLE Album 
    (
     AlbumID INTEGER PRIMARY KEY NOT NULL, 
     Title TEXT NOT NULL, 
     ArtistID INTEGER NOT NULL, 
     FOREIGN KEY (ArtistID) REFERENCES Artist (ArtistID) 
        ON DELETE NO ACTION ON UPDATE NO ACTION 
    ); 

CREATE TABLE Track 
(
    TrackID INTEGER PRIMARY KEY NOT NULL, 
    Name TEXT NOT NULL, 
    AlbumID INTEGER, 
    MediaTypeID INTEGER NOT NULL, 
    GenreID INTEGER, 
    Composer TEXT, 
    Millisecond INTEGER NOT NULL, 
    Byte INTEGER, 
    UnitPrice REAL NOT NULL, 
    FOREIGN KEY (AlbumID) REFERENCES Album (AlbumID) 
       ON DELETE NO ACTION ON UPDATE NO ACTION, 
    FOREIGN KEY (GenreID) REFERENCES Genre (GenreID) 
       ON DELETE NO ACTION ON UPDATE NO ACTION, 
    FOREIGN KEY (MediaTypeID) REFERENCES MediaType (MediaTypeID) 
       ON DELETE NO ACTION ON UPDATE NO ACTION 
); 

Answer 1

这应该完成你想要的：

SELECT a.Title, COUNT(a.Title) 
FROM Album AS a 
    JOIN Track AS b ON b.AlbumID = a.AlbumID 
GROUP BY a.AlbumID 
ORDER BY a.Title;