时间戳

首页 > 解决方案 > 创建两个表之间的除法操作困难

sql - 创建两个表之间的除法操作困难

问题描述

CREATE TABLE BARROW
(
    ID INT PRIMARY KEY,
    STUDENT_ID INT ,
    BOOK_ID INT,
)

INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (1,10010,00025)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (2,11094,00107)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (3,11094,00005)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (4,10121,00107)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (5,10020,00001)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (6,10020,00005)

CREATE TABLE BOOK
(
    ID INT PRIMARY KEY ,
    TITLE VARCHAR(100),
    AUTHOR VARCHAR(100),
    PUBLISHER VARCHAR(100),
    PUB_YEAR INT,
    ISBN_NO BIGINT,
)


INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00001,'An Intoduction to Database Systems','A.k.Alien','McGrawHill',2001,74895841)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00005,'An Intoduction to Relational Database','R.Torlone','Addison Wesley',2003,58663571)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00025,'Prgramming with visual basic','N.B.Grag','Prentice Hall',1999,851447356)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00107,'Mathematics For Engeenering','T.Date','Addison Wesley',2000,956482550)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (03567,'Fundamentals of Programming','W.L.Loure','Prentice Hall',2004,1551648)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00267,'Engeenering Mathematics','W.C.Evans','Oxford',2005,4478568)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00251,'Engeenering Mathematics','D.Woods','Prentice Hall',2003,79212655)

这些是我的表和值，我想获取所有已发行书籍的学生 ID 的记录，出版商名称为“Addison Wesley”

我要获取的结果：

( studentID,bookID(Borrow)) ÷ ( (bookID)( id ( publisher = Addison Wesley (Book))))

我想在两个表之间执行除法运算，并希望从中获取学生 ID：

SELECT STUDENT_ID, BOOK_ID 
FROM BARROW 

DIV

SELECT ID AS B 
FROM BOOK 
WHERE PUBLISHER = 'Addison Wesley'

从这个图像结果中，我想通过执行除法运算来获取所有学生的 ID...

图片

标签： sqldatabase

解决方案

您想要的是 a JOIN，我们将每个学生连接到他们已发行的书籍，然后根据该PUBLISHER字段对其进行过滤：

SELECT DISTINCT STUDENT_ID
FROM BARROW BA
JOIN BOOK BK ON BK.ID = BA.BOOK_ID
WHERE BK.PUBLISHER = 'Addison Wesley'

输出：

STUDENT_ID
11094
10121
10020

SQLFiddle 上的演示

我们DISTINCT用来避免重复行，其中一名学生发行了不止一本由Addison Wesley

推荐阅读