这是我的表格。我得到 0 行返回。
CREATE TABLE Artist (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE
);
CREATE TABLE Album (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
artist_id INTEGER,
title TEXT UNIQUE
);
CREATE TABLE Genre (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT
);
CREATE TABLE Track (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE,
album_id INTEGER,
genre_id INTEGER,
len INTEGER, rating INTEGER, count INTEGER
);
这是我的SQL代码:
SELECT Track.title, Artist.name, Album.title, Genre.name
FROM Track JOIN Genre JOIN Album JOIN Artist
ON Track.genre_id = Genre.id and Track.album_id = Album.id
WHERE Album.artist_id = Artist.id
ORDER BY Artist.name LIMIT 3
感谢您调查此问题。
到目前为止
,您的代码是正确的,但是您需要用记录填充刚刚创建的表。
例如,通过运行(未经测试):
INSERT INTO Artist (name) VALUES ('Artist 1'), ('Artist 2');
INSERT INTO Genre (name) VALUES ('Genre 1'), ('Genre 2');
INSERT INTO Album (title, artist_id) VALUES ('Title 1', 1);
INSERT INTO Track(title, album_id,
genre_id, len, rating, count)
VALUES ('Track 1', 1, 1, 0, 0, 0);
此外,还应将artist_id
、album_id
和genre_id
列声明为外键。