运行 SQL COUNT 代码 - 缺少括号

发布于 2024-12-24 15:44:34 字数 1075 浏览 2 评论 0原文

我正在尝试运行此 SQL 命令，但它给我一个错误，表明 SQL 命令未正确结束。请注意，我使用的是 Oracle 的 11g。

SELECT BookTitle.btName, BookTitle.Isbn FROM BookTitle AND SELECT COUNT(Isbn)
FROM BookTitle
JOIN Loan ON (Borrower.borId = Loan.borId)
JOIN BookCopy ON (Loan.bcId = BookCopy.bcId)
JOIN Authorship ON (BookCopy.isbn = Authorship.isbn)
JOIN Author ON (Author.authorID = Authorship.authorID)
WHERE Loan
ORDER BY Loan.dateOut DESC;

您能指导我找出问题所在吗？

在此处输入图像描述

有几个表：书名、书本、作者、借阅、作者、出版商和借阅者。我需要找到借阅最多的书并列出所有书名和 isbn。

BookTitle contains ISBN(PK), btName, datePublished, pubId*, ageLower, ageUpper, value.
Borrower contains borID(PK), borName, borAddress and borMaxbook
BOokCopy cvontains bcID(PK), ISBN*, dateAcquired, dateDestroyed
oan contains borID(PH)*, bcID(OK)*, dateOut(FK), dateDue and dateBack
Publisher contains PubId(PK), pubName and pubAddress
Publisher cotains PubID(8), pubName, pubAddress
Authoer contains authorID(PK) and authorName
Authorship contains authorId*, and ISBN*

原文

I'm trying to run this SQL command but it's giving me an error that the SQL command is not properly ended. Note, I'm using Oracle's 11g.

SELECT BookTitle.btName, BookTitle.Isbn FROM BookTitle AND SELECT COUNT(Isbn)
FROM BookTitle
JOIN Loan ON (Borrower.borId = Loan.borId)
JOIN BookCopy ON (Loan.bcId = BookCopy.bcId)
JOIN Authorship ON (BookCopy.isbn = Authorship.isbn)
JOIN Author ON (Author.authorID = Authorship.authorID)
WHERE Loan
ORDER BY Loan.dateOut DESC;

Can you kindly guide me to what's wrong ?

enter image description here

have several tables Book Title, BookCopy, Authorship, Loan, Author, Publisher and Borrower. I need to find the most borrowed book and list all titles and isbn.

BookTitle contains ISBN(PK), btName, datePublished, pubId*, ageLower, ageUpper, value.
Borrower contains borID(PK), borName, borAddress and borMaxbook
BOokCopy cvontains bcID(PK), ISBN*, dateAcquired, dateDestroyed
oan contains borID(PH)*, bcID(OK)*, dateOut(FK), dateDue and dateBack
Publisher contains PubId(PK), pubName and pubAddress
Publisher cotains PubID(8), pubName, pubAddress
Authoer contains authorID(PK) and authorName
Authorship contains authorId*, and ISBN*

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我们的影子 2024-12-31 15:44:34

SELECT 
      BookTitle.btName  AS bookName
    , BookTitle.isbn    AS ISBN
    , COUNT(*)          AS numberOfLoans 
FROM 
        BookTitle  
    JOIN 
        BookCopy   ON BookCopy.isbn = BookTitle.isbn
    JOIN 
        Loan       ON Loan.bcId = BookCopy.bcId
    JOIN 
        Borrower   ON Borrower.borId = Loan.borId
GROUP BY 
      BookTitle.btName
    , BookTitle.isbn
ORDER BY 
      MAX(Loan.dateOut) DESC         --- order by latest loan

SELECT 
      BookTitle.btName  AS bookName
    , BookTitle.isbn    AS ISBN
    , COUNT(*)          AS numberOfLoans 
FROM 
        BookTitle  
    JOIN 
        BookCopy   ON BookCopy.isbn = BookTitle.isbn
    JOIN 
        Loan       ON Loan.bcId = BookCopy.bcId
    JOIN 
        Borrower   ON Borrower.borId = Loan.borId
GROUP BY 
      BookTitle.btName
    , BookTitle.isbn
ORDER BY 
      MAX(Loan.dateOut) DESC         --- order by latest loan

回复收藏 0 原文

失退 2024-12-31 15:44:34

试试这个：

SELECT BookTitle.btName, BookTitle.Isbn, COUNT(Isbn) FROM BookTitle
JOIN Loan ON (Borrower.borId = Loan.borId)
JOIN BookCopy ON (Loan.bcId = BookCopy.bcId)
JOIN Authorship ON (BookCopy.isbn = Authorship.isbn)
JOIN Author ON (Author.authorID = Authorship.authorID)
-- WHERE Loan (Commented out because its not ended properly)
ORDER BY Loan.dateOut DESC;

编辑：看起来你的所有表格都是错误的。您的 JOIN 引用了 FROM 语句中不存在的表

EDIT2：看来您的 JOIN要么是简单的连接，要么是语法不正确的更复杂的连接。请在此处研究JOIN类型。对于简单的连接，请使用以下示例：

SELECT BookTitle.btName, BookTitle.Isbn, SELECT COUNT(Isbn) FROM BookTitle, Borrower, Loan, BookCopy, Authorship, Author
WHERE (Borrower.borId = Loan.borId)
AND (Loan.bcId = BookCopy.bcId)
AND (BookCopy.isbn = Authorship.isbn)
AND (Author.authorID = Authorship.authorID)
ORDER BY Loan.dateOut DESC;

编辑 3：检查此处和此处有关信息如何正确构建连接

Try this:

SELECT BookTitle.btName, BookTitle.Isbn, COUNT(Isbn) FROM BookTitle
JOIN Loan ON (Borrower.borId = Loan.borId)
JOIN BookCopy ON (Loan.bcId = BookCopy.bcId)
JOIN Authorship ON (BookCopy.isbn = Authorship.isbn)
JOIN Author ON (Author.authorID = Authorship.authorID)
-- WHERE Loan (Commented out because its not ended properly)
ORDER BY Loan.dateOut DESC;

EDIT: It also appears all your tables are wrong. Your JOIN's are referring to tables that do not exist in your FROM statement

EDIT2: It appears your JOIN's are either intended to be simple joins or are not properly syntax'd more complex joins. Please research here about JOIN types. For a simple join use this example:

SELECT BookTitle.btName, BookTitle.Isbn, SELECT COUNT(Isbn) FROM BookTitle, Borrower, Loan, BookCopy, Authorship, Author
WHERE (Borrower.borId = Loan.borId)
AND (Loan.bcId = BookCopy.bcId)
AND (BookCopy.isbn = Authorship.isbn)
AND (Author.authorID = Authorship.authorID)
ORDER BY Loan.dateOut DESC;

EDIT 3: Check here AND here for info on how to build your joins properly

回复收藏 0 原文