求救啊 关于mysql的问题,in和not exists返回的结果不一样啊
这是表的创建CREATETABLEMember(MemNoINTUNSIGNEDNOTNULL,DriverLicStateVARCHAR(20),DriverLicNo...
这是表的创建CREATE TABLE Member(
MemNo INT UNSIGNED NOT NULL,
DriverLicState VARCHAR(20),
DriverLicNo VARCHAR(40),
Fname VARCHAR(20),
MI VARCHAR(10),
Lname VARCHAR(20),
Address VARCHAR(250),
PhoneNumber VARCHAR(15),
PRIMARY KEY (MemNo));
CREATE TABLE Book(
Book_ID INT UNSIGNED NOT NULL,
Edition VARCHAR(80),
BorrowerMemNo INT UNSIGNED,
BorrowDueDate DATE,
CallNumber VARCHAR(40),
LibCheck INT UNSIGNED,
PRIMARY KEY (Book_ID),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber),
FOREIGN KEY (BorrowerMemNo) REFERENCES Member(MemNo),
FOREIGN KEY (LibCheck) REFERENCES Librarian(SSN));
CREATE TABLE Author(
CallNumber VARCHAR(40) NOT NULL,
Fname VARCHAR(40) NOT NULL,
MI VARCHAR(10),
Lname VARCHAR(40) NOT NULL,
PRIMARY KEY (CallNumber, Fname, Lname),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));
这是我的两个查询:
SELECT
Member.MemNo,
Member.Fname,
Member.Lname
FROM
Member
WHERE
Member.MemNo IN (
SELECT
Book.BorrowerMemNo
FROM
Book
WHERE
Book.CallNumber IN (
SELECT
Author.CallNumber
FROM
Author
WHERE
Author.Lname = 'Collins'
)
)
SELECT
memno,
fname,
lname
FROM
member
WHERE
NOT EXISTS (
SELECT
*
FROM
author
WHERE
author.lname = 'Collins'
AND NOT EXISTS (
SELECT
*
FROM
book
WHERE
member.memno = book.borrowermemno
AND author.callnumber = book.callnumber
)
); 展开
MemNo INT UNSIGNED NOT NULL,
DriverLicState VARCHAR(20),
DriverLicNo VARCHAR(40),
Fname VARCHAR(20),
MI VARCHAR(10),
Lname VARCHAR(20),
Address VARCHAR(250),
PhoneNumber VARCHAR(15),
PRIMARY KEY (MemNo));
CREATE TABLE Book(
Book_ID INT UNSIGNED NOT NULL,
Edition VARCHAR(80),
BorrowerMemNo INT UNSIGNED,
BorrowDueDate DATE,
CallNumber VARCHAR(40),
LibCheck INT UNSIGNED,
PRIMARY KEY (Book_ID),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber),
FOREIGN KEY (BorrowerMemNo) REFERENCES Member(MemNo),
FOREIGN KEY (LibCheck) REFERENCES Librarian(SSN));
CREATE TABLE Author(
CallNumber VARCHAR(40) NOT NULL,
Fname VARCHAR(40) NOT NULL,
MI VARCHAR(10),
Lname VARCHAR(40) NOT NULL,
PRIMARY KEY (CallNumber, Fname, Lname),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));
这是我的两个查询:
SELECT
Member.MemNo,
Member.Fname,
Member.Lname
FROM
Member
WHERE
Member.MemNo IN (
SELECT
Book.BorrowerMemNo
FROM
Book
WHERE
Book.CallNumber IN (
SELECT
Author.CallNumber
FROM
Author
WHERE
Author.Lname = 'Collins'
)
)
SELECT
memno,
fname,
lname
FROM
member
WHERE
NOT EXISTS (
SELECT
*
FROM
author
WHERE
author.lname = 'Collins'
AND NOT EXISTS (
SELECT
*
FROM
book
WHERE
member.memno = book.borrowermemno
AND author.callnumber = book.callnumber
)
); 展开
1个回答
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询