它说我没有选择行? [英] Its saying I have no row selected?

问题描述

这是说我没有选择任何行.

It's saying I have no row selected.

这是问题: 查找过去或当前从未借过任何书籍的会员的会员ID，姓氏和名字.

This is the question: Find the member ID, last name, and first name of the members who have never borrowed any books in the past or currently.

这是架构，主键为粗体.

This is the schema, Primary Keys are bold.

Book( bookID ，ISBN，书名，作者，出版年份，类别)

Book(bookID, ISBN, title, author, publish-year, category)

会员(会员ID ，姓氏，名字，地址，电话号码，限制)

Member(memberID, lastname, firstname, address, phone-number, limit)

CurrentLoan(会员ID ， bookID ，贷款日期，到期日)

CurrentLoan(memberID, bookID, loan-date, due-date)

历史记录(会员ID ，图书ID ，借阅日期，归还日期)

History(memberID, bookID, loan-date, return-date)

会员可以从图书馆借书.他们可以借用的书籍数量受会员关系的限制"字段限制(对于不同的会员可能有所不同).一本书的类别包括小说，非小说，儿童小说和参考书. CurrentLoan表表示有关当前已签出的书籍的信息.当这本书返回图书馆时，该记录将从CurrentLoad关系中删除，并将与返回日期一起插入History关系中.图书馆可能拥有同一本书的多个副本，在这种情况下，每个副本都有自己的bookID，但是所有副本都共享相同的ISBN.

Members can borrow books from the library. The number of books they can borrow is limited by the "limit" field of the Member relation (it may differ for different members). The category of a book includes fiction, non-fiction, children’s and reference. The CurrentLoan table represents the information about books that are currently checked out. When the book is returned to the library, the record will be removed from CurrentLoad relation, and will be inserted into History relation with the return-date. A library may have more than one copy of the same book, in which case each copy has its own bookID, but all copies share the same ISBN.

这是我的代码:

CREATE TABLE Book
    (bookID INT,
    ISBN INT,
    title varchar (25),
    author  varchar (20),
    publish_year INT,
    category varchar(20),
    PRIMARY KEY (bookID));  

CREATE TABLE Member
    (memberID INT,
    lastname varchar (20),
    firstname varchar (20),
    address varchar(20),
    phone_number INT,   
    limit_ INT,
    PRIMARY KEY (memberID));

CREATE TABLE CurrentLoan
    (memberID INT ,
    bookID INT,
    loan_date DATE,
    due_date DATE,
    PRIMARY KEY (memberID, bookID),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));


CREATE TABLE History
    (memberID INT,
    bookID INT,
    loan_date DATE,
    return_date DATE,
    PRIMARY KEY (memberID, bookID, loan_date),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

INSERT INTO Book VALUES (10, 1113312336, 'The Dog', 'Jack Crow', 1990, 'fiction');
INSERT INTO Book VALUES (12, 2221254896, 'Worms', 'Jim Kan', 2013, 'childrens');
INSERT INTO Book VALUES (13, 3332546987, 'Crow', 'Jan Flo', 2000, 'fiction'); 
INSERT INTO Book VALUES (14, 4443456215, 'Big Dog', 'Lan Big', 1993, 'children'); 
INSERT INTO Book VALUES (15, 5552314569, 'Green Apple', 'Theo Brown', 1978, 'children');
INSERT INTO Book VALUES (16, 6664581631, 'Red Bean', 'Khang Nk', 2017, 'fiction');
INSERT INTO Book VALUES (17, 7771452369, 'XML and XQuery Knowledge', 'Author Le', 2017, 'non-fiction');
INSERT INTO Book VALUES (18, 8881245525, 'The Dark Room', 'Jack Se', 2017, 'fiction');
INSERT INTO Book VALUES (19, 9991123546, 'Lonely Mens', 'Geen Brown', 2014, 'refrence');
INSERT INTO Book VALUES (20, 1122112356, 'XML or XQuery', 'Heart Le', 2002, 'fiction');

INSERT INTO Member VALUES (001, 'Lee', 'Nancy', 'Brownlea Drive', 1254896325, 2);
INSERT INTO Member VALUES (002, 'Le', 'Ray', '10th Street', 1234561256, 2);
INSERT INTO Member VALUES (003, 'Kan', 'Charlie', '5th Street', 1234567236, 2);
INSERT INTO Member VALUES (004, 'Brown', 'Joe', 'Elm Street', 1234567845, 2);
INSERT INTO Member VALUES (005, 'Smith', 'John', '33 East', 1234567890, 2);

INSERT INTO CurrentLoan VALUES (005, 10, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (005, 19, '13-JAN-17', '15-NOV-17');
INSERT INTO CurrentLoan VALUES (003, 16, '14-FEB-17', '12-MAR-17');
INSERT INTO CurrentLoan VALUES (004, 15, '12-OCT-17', '09-NOV-17');
INSERT INTO CurrentLoan VALUES (005, 18, '13-APR-17', '12-MAY-17');

INSERT INTO History VALUES (001, 10, '14-Jan-17', '04-OCT-17');
INSERT INTO History VALUES (002, 19, '12-Jan-17', '04-NOV-17');
INSERT INTO History VALUES (003, 13, '14-APR-17', '08-OCT-17');
INSERT INTO History VALUES (005, 20, '14-MAY-17', '04-DEC-17');

我的查询是:

SELECT Member.memberID, lastname, firstname 
    FROM Member MINUS(
                    SELECT Member.memberID, lastname, firstname
                    FROM Member, CurrentLoan
                    WHERE Member.memberID = CurrentLoan.memberID
                    UNION
                    SELECT Member.memberID, lastname, firstname
                    FROM Member, History
                    WHERE Member.memberID = History.memberID);

推荐答案

有两种方法可以做到这一点:

There's a couple of ways to do this:

您可以使用反联接，例如:

You can use an anti-join, such as:

SELECT m.MEMBERID,
       m.LASTNAME,
       m.FIRSTNAME
  FROM MEMBERS m
  WHERE m.MEMBERID NOT IN (SELECT DISTINCT MEMBERID
                             FROM CURRENTLOAN
                           UNION ALL
                           SELECT DISTINCT MEMBERID
                             FROM HISTORY);

另一种方法(也是我的首选方法)是:

Another way (and my preferred method) to do what you want is:

SELECT DISTINCT m.MEMBERID,
                m.LASTNAME,
                m.FIRSTNAME
  FROM MEMBERS m
  LEFT OUTER JOIN (SELECT DISTINCT MEMBERID
                     FROM (SELECT MEMBERID
                             FROM CURRENTLOAN
                           UNION ALL
                           SELECT MEMBERID
                             FROM HISTORY)) u
    ON u.MEMBERID = m.MEMBERID
  WHERE u.MEMBERID IS NULL;

但是，给定您显示的查询数据以及原始查询，该数据应返回零行. 此处的SQLFiddle

However, given the data you've shown this query, as well as your original query, should return zero rows. SQLFiddle here

请注意，如果您注释掉当前向成员004发放的贷款，则会返回乔·布朗" SQLFiddle在这里

Note that if you comment out the current loan to member 004, then "Joe Brown" is returned SQLFiddle here

好运.

这篇关于它说我没有选择行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！