时间戳

首页 > 解决方案 > 图书馆数据库

sql - 图书馆数据库

问题描述

在我的数据库中，我有五个表：books、magazines、customer、reservation、items_brrwd。如果有人借书，数据将存储在item_brrwd表中。我怎样才能得到借得最多的书？

我需要你的帮助。

这是我的数据库。

图书。

 CREATE TABLE "M_5126582"."BOOKS" 
   (    "B_ID" NUMBER(*,0) NOT NULL ENABLE, 
    "TITLE" VARCHAR2(45 BYTE) NOT NULL ENABLE, 
    "AUTHOR" VARCHAR2(45 BYTE) NOT NULL ENABLE, 
    "PUBLISHER" VARCHAR2(45 BYTE) NOT NULL ENABLE, 
    "YEAR" DATE NOT NULL ENABLE, 
    "GENRE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "NOPAGES" NUMBER(*,0) NOT NULL ENABLE, 
     CONSTRAINT "BOOKS_PK" PRIMARY KEY ("B_ID")
);

items_brrwd。

CREATE TABLE "M_5126582"."ITEM_BRRWD" 
(   "ITEMBORROWED_ID" NUMBER(*,0) NOT NULL ENABLE, 
"CUSTOMER_ID" NUMBER(*,0) NOT NULL ENABLE, 
"BOOK_ID" NUMBER(*,0), 
"NUM_BOOK" NUMBER(*,0), 
"MAGAZINE_ID" NUMBER(*,0), 
"NUM_MAGAZINES" NUMBER(*,0), 
"BRRWD_DATE" DATE, 
"RETURN_DATE" DATE, 
"EBOOK_DOWNLOAD_DATE" DATE, 
 CONSTRAINT "ITEM_BRRWD_PK" PRIMARY KEY ("ITEMBORROWED_ID")
);

顾客。

CREATE TABLE "M_5126582"."CUSTOMER" 
(   "CUSTOMERID" NUMBER(*,0) NOT NULL ENABLE, 
"NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
"ADDRESS" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
"ITEMNOBRRWD" NUMBER(*,0), 
"REGISTRATIONDATE" DATE NOT NULL ENABLE, 
"NUM_BOOKS" NUMBER(*,0), 
"NUM_MAGAZINES" NUMBER(*,0), 
 CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMERID")) ;

杂志。

CREATE TABLE "M_5126582"."MAGAZINES" 
(   "M_ID" NUMBER(*,0) NOT NULL ENABLE, 
"TITLE" VARCHAR2(45 BYTE) NOT NULL ENABLE, 
"ISSUEDATE" DATE NOT NULL ENABLE, 
 CONSTRAINT "MAGAZINES_PK" PRIMARY KEY ("M_ID")
);

预订。

CREATE TABLE "M_5126582"."RESERVATION" 
(   "CUSTOMERID" NUMBER(*,0) NOT NULL ENABLE, 
"ITEMID" NUMBER(*,0) NOT NULL ENABLE, 
"NOINTHEQUEUE" NUMBER(*,0) NOT NULL ENABLE, 
 CONSTRAINT "RESERVATION_PK" PRIMARY KEY ("CUSTOMERID")
);

标签： sqloracle

解决方案

没有测试过，但这可能有效。
这将找到借阅次数最多的书名。 

SELECT
 bk.TITLE, 
 COUNT(*) AS TotalBorrowed
FROM M_5126582.ITEM_BRRWD brrwd
JOIN M_5126582.BOOKS bk ON bk.B_ID = brrwd.BOOK_ID
GROUP BY bk.TITLE
ORDER BY COUNT(*) DESC
FETCH FIRST ROW ONLY

还是老款TOP n

SELECT * 
FROM (
  SELECT bk.TITLE, 
  COUNT(*) AS TotalBorrowed
  FROM M_5126582.ITEM_BRRWD brrwd
  JOIN M_5126582.BOOKS bk ON bk.B_ID = brrwd.BOOK_ID
  GROUP BY bk.TITLE
  ORDER BY COUNT(*) DESC
) q
WHERE ROWNUM <= 1

推荐阅读