MySQL删除值如果存在另一行
好的，我有一个问题。
我有作者和书之间的多对多关系，它创建了一个我已经命名为CoverPage的Junction表。
Author:
AuthorID
AuthorName
CoverPage: (Junction Table)
AuthorID
BookID
Book:
BookID
BookTitle
BookType
问题：创建显示所有撰写小说书籍的作者的陈述，但不显示任何创建了历史书籍的作者。
例如：
AuthorName: BookName: Book Type:
John Smith The Awakening Fiction
John Smith WW2 Historical
Jack Johnson The last Alien Fiction
查询应该只显示杰克约翰逊，因为约翰史密斯即使他创造了一本小说书，他也创造了一本历史书，但不幸的是，在我的疑问中，他仍然出现。
就像是：
Select distinct AuthorName
From Author, Book, CoverPage
Where Author.AuthorID=CoverPage.AuthorID
And Book.BookID=CoverPage.BookID
And BookType = 'Fiction' And Not BookType = 'Historical';
要么
SELECT distinct AuthorName, EmailAddress
FROM Author
LEFT JOIN CoverPage ON Author.AuthorID = CoverPage.Author_AuthorID
LEFT JOIN Book ON CoverPage.Book_BookID = Book.BookID
Where Not BookType = 'Historical'
Group By AuthorID;
这是我的表格：
CREATE TABLE IF NOT EXISTS `Publisher` (
`PublisherID` VARCHAR(3) NOT NULL,
`PublisherName` VARCHAR(30) NULL,
`City` VARCHAR(15) NULL,
`ContactNumber` VARCHAR(10) NULL,
PRIMARY KEY (`PublisherID`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `Author` (
`AuthorID` INT NOT NULL,
`AuthorName` VARCHAR(20) NULL,
`EmailAddress` VARCHAR(25) NULL,
PRIMARY KEY (`AuthorID`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `Book` (
`BookID` INT NOT NULL,
`BookTitle` VARCHAR(30) NULL,
`BookType` VARCHAR(10) NULL,
`Price` FLOAT NULL,
`PaperBack?` VARCHAR(5) NULL,
`Publisher_PublisherID` VARCHAR(3) NOT NULL,
PRIMARY KEY (`BookID`),
INDEX `fk_Book_Publisher1_idx` (`Publisher_PublisherID` ASC),
CONSTRAINT `fk_Book_Publisher1`
FOREIGN KEY (`Publisher_PublisherID`)
REFERENCES `Publisher` (`PublisherID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `CoverPage` (
`Book_BookID` INT NOT NULL,
`Author_AuthorID` INT NOT NULL,
PRIMARY KEY (`Book_BookID`, `Author_AuthorID`),
INDEX `fk_Book_has_Author_Author1_idx` (`Author_AuthorID` ASC),
INDEX `fk_Book_has_Author_Book1_idx` (`Book_BookID` ASC),
CONSTRAINT `fk_Book_has_Author_Book1`
FOREIGN KEY (`Book_BookID`)
REFERENCES `Book` (`BookID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Book_has_Author_Author1`
FOREIGN KEY (`Author_AuthorID`)
REFERENCES `Author` (`AuthorID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
insert into Publisher values ('P11','Eureka Publishing', 'Parramatta', '9981 3909' );
insert into Publisher values ('P12','Lishpub Book Publishing', 'Penrith', '9971 3000' );
insert into Publisher values ('P13','The Publisher Corp', 'Gosford', '9931 2999' );
insert into Publisher values ('P15','Stewart Publishing', 'Bankstown', '9931 7000' );
insert into Publisher values ('P14','George Street Publishing', 'Sydney CBD', '9981 4111' );
insert into Author values (321, 'Barry White', 'b.white@gmail.com');
insert into Author values (322, 'Bill Cosby', 'billc@hotmail.com');
insert into Author values (323, 'George Ryan', 'gryan@yahoo.com');
insert into Author values (324, 'Jon Snow', 'jsnow@yahoo.com');
insert into Book values (4321, 'The Awakening', 'Historical', '20.00', 'yes', 'P11');
insert into Book values (4322, 'Indianna Jones', 'Fiction', '30.00', 'yes', 'P12');
insert into Book values (4324, 'Star Wars', 'Fiction', '20.00', 'yes', 'P14');
insert into Book values (4323, 'The Lost Magic', 'Education', '10.00', 'no', 'P13');
insert into Book values (4325, 'World War', 'Historical', '20.00', 'yes', 'P14');
insert into Book values (4326, 'Doctor No', 'Fiction', '25.00', 'yes', 'P12');
insert into Book values (4327, 'Spiderman', 'Fiction', '10.00', 'no', 'P12');
insert into CoverPage values (4321, 321);
insert into CoverPage values (4321, 323);
insert into CoverPage values (4324, 322);
insert into CoverPage values (4325, 322);
insert into CoverPage values (4324, 323);
insert into CoverPage values (4326, 323);
insert into CoverPage values (4327, 323);
insert into CoverPage values (4327, 324);
考虑NOT EXISTS子句：
SELECT a.AuthorName
FROM Author a
INNER JOIN CoverPage c ON a.AuthorID = c.AuthorID
INNER JOIN Book b ON b.BookID = c.BookID AND b.BookType = 'Fiction'
WHERE NOT EXISTS
(SELECT 1 FROM Author sub_a
INNER JOIN CoverPage sub_c
ON sub_a.AuthorID = sub_c.AuthorID
INNER JOIN Book sub_b
ON sub_b.BookID = sub_c.BookID AND sub_b.BookType = 'Historical'
WHERE sub_a.AuthorID = a.AuthorID);
您可以通过使用NOT IN并获取已写入历史书籍并将其排除在主查询中的所有作者的列表来完成此操作。
SELECT DISTINCT
a.AuthorName
FROM Author a
JOIN CoverPage cp on cp.author_AuthorID = a.AuthorID
JOIN Book b on cp.book_BookID = b.BookID
WHERE b.BookType = 'Fiction'
AND a.AuthorID NOT IN
(SELECT cp.author_AuthorID
FROM CoverPage cp
JOIN Book b ON cp.book_BookID = b.BookID
WHERE BookType = 'Historical')
SQL小提琴
链接地址: http://www.djcxy.com/p/63805.html