SQL查询：如何改进？

debugcn 发表于 Dev

德里克

我在MySQL数据库中有两个表：

Book(title, publisher, year) title is primary key

Author(name, title) title is foreign key to Book

我试图选择从2000年到2005年（每年）出版一本书的作者的姓名。此SQL查询有效，但是有没有办法做到这一点，如果需要，可以更轻松地更改日期范围？

SELECT DISTINCT name
FROM Author
WHERE name IN  (SELECT Author.name 
                FROM Author INNER JOIN Book ON (Author.title = Book.title) 
                WHERE year = 2000)
        AND name IN
                (SELECT Author.name 
                FROM Author INNER JOIN Book ON (Author.title = Book.title)
                WHERE year = 2001)
        AND name IN
                (SELECT Author.name 
                FROM Author INNER JOIN Book ON (Author.title = Book.title)
                WHERE year = 2002)
        AND name IN
                (SELECT Author.name 
                FROM Author INNER JOIN Book ON (Author.title = Book.title)
                WHERE year = 2003)
        AND name IN
                (SELECT Author.name 
                FROM Author INNER JOIN Book ON (Author.title = Book.title)
                WHERE year = 2004)
        AND name IN
                (SELECT Author.name 
                FROM Author INNER JOIN Book ON (Author.title = Book.title)
                WHERE year = 2005);

这有两种实现方法，以及另一种方法由于细微的错误而出错的方法。

SQL小提琴

MySQL 5.5.32模式设置：

create table Book (title varchar(10), year int) ;
create table Author (name varchar(10), title varchar(10));

insert Book values 
('Book1',2000),('Book2',2000),
('Book3',2000),('Book4',2000),
('Book5',2000),('Book6',2000),
('Book7',2001),('Book8',2002),
('Book9',2003),('Book10',2004),
('Book11',2005);

insert into Author values 
('Author1','Book1'),('Author1','Book2'),
('Author1','Book3'),('Author1','Book4'),
('Author1','Book5'),('Author1','Book6'),
('Author2','Book6'),('Author2','Book7'),
('Author2','Book8'),('Author2','Book9'),
('Author2','Book10'),('Author2','Book11');

# author1 has written 6 books in one year
# author2 has written 1 book in every of the six years

查询1：

# incorrect as it matches author1 who has 6 books in a single year
SELECT name from Author 
INNER JOIN BOOK on Author.title = Book.Title 
WHERE year IN (2000,2001,2002,2003,2004,2005) 
GROUP BY name 
HAVING COUNT(name) = 6

结果：

|    NAME |
|---------|
| Author1 |
| Author2 |

查询2：

# correct as it counts distinct years
SELECT name from Author 
INNER JOIN BOOK on Author.title = Book.Title 
WHERE year IN (2000,2001,2002,2003,2004,2005) 
GROUP BY name 
HAVING COUNT(DISTINCT year) = 6

结果：

|    NAME |
|---------|
| Author2 |

查询3：

# correct using relational division
SELECT DISTINCT name
FROM Author A1
INNER JOIN Book B1 ON A1.title = B1.Title
WHERE NOT EXISTS (
    SELECT * 
    FROM Book B2
    WHERE year IN (2000,2001,2002,2003,2004,2005) 
    AND NOT EXISTS (
       SELECT *
       FROM Author A2
       INNER JOIN Book B3 ON A2.title = B3.Title
       WHERE (A1.name = A2.name)
        AND (B3.year = B2.year)
    )
)