SQL条件查询：按CP值控制列显示并过滤借阅记录

数据表结构及初始数据 #

CREATE TABLE books
(
    codBook INTEGER PRIMARY KEY,
    title   CHAR(20) NOT NULL
);

INSERT INTO books 
VALUES (1, 'Book 1'), (2, 'Book 2'), (3, 'Book 3');

CREATE TABLE people
(
    name    CHAR(10) PRIMARY KEY,
    address VARCHAR(50),
    CP      NUMERIC(5)
);  

INSERT INTO people 
VALUES ('Carl', 'C/X nº 1', '12345'), ('Louis', 'C/X nº 2', '12345'), 
       ('Joseph', 'C/Y nº 3', '12346'), ('Anna', 'C/Z nº 4', '12347');

CREATE TABLE lends
(
    codBook INTEGER REFERENCES books,
    member  CHAR(10) REFERENCES people,
    date    DATE,

    PRIMARY KEY (codBook, member, date)
);

INSERT INTO lends 
VALUES (1, 'Joseph', CURRENT_DATE - 10), 
       (1, 'Carl', CURRENT_DATE - 9), 
       (1, 'Louis', CURRENT_DATE - 8), 
       (2, 'Joseph', CURRENT_DATE - 10); 

需求说明 #

查询所有图书的title、address、CP列，规则如下：

• 仅当借阅者的CP=12345时，显示对应的address和CP值；
• 借阅者CP≠12345的记录不显示；
• 未被借阅的图书，address和CP显示为null。

预期结果：

"Book 1";"C/X nº 1";12345
"Book 1";"C/X nº 2";12345
"Book 2";null;null
"Book 3";null;null

错误尝试及问题 #

使用两次LEFT JOIN加WHERE筛选的SQL：

SELECT title, address, CP 
FROM books 
LEFT JOIN lends USING (codBook) 
LEFT JOIN people ON (name = member) 
WHERE CP = 12345;

该语句仅能得到CP=12345的记录，丢失了未被借阅的图书（Book 2、Book 3）；若移除WHERE条件，则会包含Book 1对应CP=12346的不符合要求的记录。

解决方案 #

方法一：在关联people表时加入CP筛选 #

SELECT b.title, p.address, p.CP
FROM books b
LEFT JOIN lends l ON b.codBook = l.codBook
LEFT JOIN people p ON l.member = p.name AND p.CP = 12345
GROUP BY b.title, p.address, p.CP
HAVING p.CP IS NOT NULL OR COUNT(l.codBook) = 0;

方法二：先筛选符合条件的借阅记录再关联 #

SELECT b.title, p.address, p.CP
FROM books b
LEFT JOIN (
    SELECT l.codBook, l.member
    FROM lends l
    JOIN people p ON l.member = p.name
    WHERE p.CP = 12345
) filtered_lends ON b.codBook = filtered_lends.codBook
LEFT JOIN people p ON filtered_lends.member = p.name;

原理说明 #

• 方法一中，将p.CP=12345加入LEFT JOIN people的关联条件，仅符合CP要求的借阅者会被关联，不符合的直接排除，未被借阅的图书也会保留，address和CP显示为null。GROUP BY和HAVING用于去重，避免同一本未符合条件的图书生成多条重复null记录。
• 方法二中，先通过子查询筛选出所有CP=12345的借阅记录，再与图书表关联，关联不到的图书自然显示null，同时不会引入不符合条件的借阅记录。

内容的提问来源于stack exchange，提问作者itsLOKO