sql - SQL 连接查询到库
问题描述
我有这些桌子,
CREATE TABLE Book
(
ISBN CHAR(05)NOT NULL,
BKName VARCHAR(20)NOT NULL,
Author VARCHAR(20)NOT NULL,
Price NUMERIC(02)NOT NULL,
CONSTRAINT Book_PK PRIMARY KEY (ISBN)
);
CREATE TABLE Location
(
LoID CHAR(05)NOT NULL,
CityName VARCHAR(15)NOT NULL,
Stoke CHAR(05)NOT NULL,
CONSTRAINT Location_PK PRIMARY KEY (LoID)
);
CREATE TABLE Customer
(
CuID CHAR(05)NOT NULL,
CusName VARCHAR(20)NOT NULL,
RegiDate DATE NOT NULL,
Gender VARCHAR(06)NOT NULL,
TeleNum CHAR(12)NOT NULL,
Address VARCHAR(30)NOT NULL,
CONSTRAINT Customer_PK PRIMARY KEY (CuID)
);
CREATE TABLE BookCopy
(
CopyID CHAR(05)NOT NULL,
ISBN CHAR(05)NOT NULL,
LoID CHAR(05)NOT NULL,
CONSTRAINT pk_BookCopy PRIMARY KEY (CopyID),
CONSTRAINT fk_BookCopy_ISBN_FK FOREIGN KEY (ISBN) REFERENCES Book(ISBN),
CONSTRAINT fk_BookCopy_LoID_FK FOREIGN KEY (LoID) REFERENCES Location(LoID)
);
CREATE TABLE BorrowRecord
(
BrrDate DATE NOT NULL,
RetDate DATE NOT NULL,
BrFee NUMBER(05) NOT NULL,
Cus_Review NUMERIC(02)NOT NULL,
CopyID CHAR(05)NOT NULL,
CuID CHAR(05) NOT NULL,
CONSTRAINT pk_BorrowRecord PRIMARY KEY (CopyID, CuID),
CONSTRAINT fk_BorrowRecord_CopyID_FK FOREIGN KEY (CopyID) REFERENCES BookCopy(CopyID) ,
CONSTRAINT fk_BorrowRecord_CuID_FK FOREIGN KEY (CuID) REFERENCES Customer(CuID) );
这是任务:“编写并测试查询以列出每个客户的客户 ID 和姓名以及他们在过去 200 天内雇用的书籍。包括这些雇用的开始日期、结束日期和地点名称。所有客户详细信息(ID 和姓名)应包含在输出中,无论他们是否实际借过任何书籍。”
我写了一个查询来列出每个客户的客户 ID 和姓名以及他们在过去 200 天内借阅的书籍。包括这些招聘的开始日期、结束日期和地点名称。这是它的查询。
SELECT
BorrowRecord.CuID, Customer.CusName, Book.ISBN, Book.BkName,
BorrowRecord.BrrDate, BorrowRecord.RetDate, Location.CityName
FROM
Book
LEFT JOIN
BookCopy ON Book.ISBN = BookCopy.ISBN
LEFT JOIN
BorrowRecord ON BookCopy.CopyID = BorrowRecord.CopyID
LEFT JOIN
Customer ON BorrowRecord.CuID = Customer.CuID
LEFT JOIN
Location ON Location.LoID = BookCopy.LoID
WHERE
BorrowRecord.BrrDate >= sysdate - 200 ;
但我还需要获取应该包含在输出中的所有客户详细信息(ID 和姓名),无论他们是否实际借过任何书籍。我该怎么做?
解决方案
如果您想独立查看所有客户,无论他们是否借过一本书,重要的是第一个 LEFT JOIN 是基于客户表的。在您的脚本中,您从 Book 开始,然后加入 BorrowRecord,然后才在加入的右侧有 The Customer。另一点是 WHERE 条件,它排除了在 200 天范围之外借书的所有客户条目。这意味着会显示 BorrowRecord 的所有记录(与之前的联接匹配),但仅显示过去 200 天的 BorrowRecords 链接中的客户。尝试以下操作:
SELECT BorrowRecord.CuID, Customer.CusName, Book.ISBN, Book.BkName,
BorrowRecord.BrrDate,BorrowRecord.RetDate, Location.CityName
FROM Customer
LEFT JOIN BorrowRecord ON BorrowRecord.CuID = Customer.CuID
LEFT JOIN BookCopy ON BorrowRecord.CopyID = BookCopy.CopyID
LEFT JOIN Book ON BookCopy.ISBN= Book.ISBN
LEFT JOIN Location ON Location.LoID = BookCopy.LoID
WHERE ISNULL(BorrowRecord.BrrDate,'') >=sysdate-200 ;
推荐阅读
- regex - 在 Perl 中的某个特定匹配项下方编辑/匹配字符串 n 行?
- haskell - 块缩进会导致错误吗?
- html - Sticky footer with background image
- python - 如何从scala中的avro文件中读取元数据
- html - How do I stack two rows on top of each other using Bootstrap?
- sparql - Virtuoso SPARQL Bug 与 BIND
- c# - 如何在 Azure Function 中定义“外部”StorageAccount?
- php - How to setup an existing php project which used composer in a localhost with xampp?
- css - How to make table width auto with d3.js
- java - Removing list of objects from repository with Spring Data usage