给出每本书存放的城市名称

jfgube3f 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(295)

架构：

author(authorname, citizenship,birthyear)

book(ISBN, title, authorname)

topic(ISBN, subject)

branch(libname, city)

instock(ISBN, libname, quantity)

请对以下问题提出疑问
1）给出可以找到所有书籍的城市名称（每个书名至少有一本书）。
2）给作者和标题的每一本书有最高的副本在墨尔本分行。
我试着解决第二个问题，如果我错了就纠正我

SELECT author,title
FROM book INNER JOIN instock ON book.ISBN=instock.ISBN
GROUP BY city HAVING city='melbourne' AND quantity= (
    SELECT MAX(quantity)
    FROM instock
    GROUP BY city Having city='melbourne'
)

sql mysql Database

来源：https://stackoverflow.com/questions/49925099/give-name-of-city-where-every-book-is-stored

1条答案

按热度按时间

vkc1a9a21#

由于您尝试了一些方法，我提出了以下问题以提供帮助：
给出可以找到所有书籍的城市名称（每个书名至少有一本书）。

SELECT A.city
FROM branch A JOIN instock B
ON A.libname=B.libname
WHERE B.quantity>0
GROUP BY A.city
HAVING COUNT(DISTINCT B.ISBN)=(SELECT COUNT(DISTINCT ISBN) FROM book);

给作者和标题的每一本书有最高的副本在墨尔本分行。

SELECT DISTINCT H.ISBN, H.title, H.authorname
FROM 
    (SELECT MAX(C.totalQuantity) maxTotalQuantity
    FROM
    (SELECT A.ISBN, SUM(B.quantity) totalQuantity
     FROM branch A JOIN instock B
     ON A.libname=B.libname
     WHERE LOWER(A.city)='melbourne'
     GROUP BY A.ISBN) C) F 
JOIN 
    (SELECT D.ISBN, SUM(E.quantity) totalQuantity
     FROM branch D JOIN instock E
     ON D.libname=E.libname
     WHERE LOWER(D.city)='melbourne'
     GROUP BY D.ISBN) G
ON F.maxTotalQuantity=G.totalQuantity
JOIN book H 
ON H.ISBB=G.ISBN;

赞(0）回复(0）举报 2021-06-24

我来回答

给出每本书存放的城市名称

1条答案

相关问题

热门标签

最新问答