mysql - 从 3 个表查看

Question

我需要一些帮助来创建一个 View ，显示从当前日期起过去 20 年内未发布歌曲的艺术家

提前致谢这些是表格:

CREATE TABLE artist (
    id TINYINT(4) AUTO_INCREMENT PRIMARY KEY,
    surname VARCHAR(30) DEFAULT 'doe',
    name VARCHAR(30) DEFAULT 'jhon'
);

INSERT INTO artist(surname, name) VALUES
            ('Jackson','Michael'),
            ('Estefan', 'Gloria'),
            ('Turner', 'Tina'),
            ('Twaim','Shanya');

CREATE TABLE songs (
    id TINYINT(4) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30)
);

INSERT INTO songs(title) VALUES
            ('Simply the best'),
            ('What`s love got to do with?'),
            ('Private Dancer'),
            ('Bille Jean'),
            ('Give in to me'),
            ('From this moment on'),
            ('Any man of mine');

CREATE TABLE discografy (
        id TINYINT(4) AUTO_INCREMENT PRIMARY KEY,
        id_artist TINYINT(4),
        id_song TINYINT(4),
        year_aparition YEAR(4),
        duration SMALLINT(7)
);

INSERT INTO discografy(id_artist, id_song, year_aparition, duration) VALUES
             (1,4,2008,296),
             (1,5,1991,331),
             (3,1,1989,330),
             (3,2,1984,236),
             (3,3,1984,252),
             (4,6,1998,237),
             (4,7,1995,262);

Answer 1

CREATE VIEW view_name AS
SELECT A.surname,A.name
FROM artist A
JOIN 
discografy D
On A.id = D.id_artist
where D.year_aparition < (YEAR(CURDATE()) - 20)

使用此链接来帮助您处理数据格式: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_current-date