如何为SECONDARYBOOKS表添加status列，依据PrimaryBooks匹配设0或1？

我有一个名为Books的数据库，包含两张表：

PrimaryBooks表数据 #

Title            Pages  DateOfRelease
Sapiens          572    1987-01-20
Black Swan       852    2004-07-14
1984             429    1949-12-03
Influence        1582   2017-06-17
Freakonomics     826    2014-11-26
The Alchemist    371    2001-02-07
Quiet            639    2016-08-29
Blink            198    2022-04-11
The Art of War   2485   1772-09-10

SECONDARYBOOKS表数据 #

该表结构类似但多了COLOR和PRICE列，其余列名与PrimaryBooks一致但为大写：

COLOR    PRICE  TITLE           PAGES  DATEOFRELEASE
Green    17     Sapiens         834    1987-01-20
Black    25     Black Swan      852    2004-07-14
Gray     28     1984            429    1949-12-03
Orange   12     Influence       1582   2018-05-16
Red      31     Freakonomics    826    2014-11-26
White    37     The Alchemist   371    2001-02-07
Cyan     10     Meditations     1004   2016-08-29
Blue     18     Deep Work       624    2023-01-12
Yellow   29     The Art of War  2485   1672-09-10

需求 #

为SECONDARYBOOKS表添加status列：当该行除COLOR和PRICE外的字段（TITLE、PAGES、DATEOFRELEASE）与PrimaryBooks中某行完全一致时，status设为1，否则设为0，最终结果如下：

COLOR    PRICE  TITLE           PAGES  DATEOFRELEASE  STATUS
Green    17     Sapiens         834    1987-01-20     0
Black    25     Black Swan      852    2004-07-14     1
Gray     28     1984            429    1949-12-03     1
Orange   12     Influence       1582   2018-05-16     0
Red      31     Freakonomics    826    2014-11-26     1
White    37     The Alchemist   371    2001-02-07     1
Cyan     10     Meditations     1004   2016-08-29     0
Blue     18     Deep Work       624    2023-01-12     0
Yellow   29     The Art of War  2485   1672-09-10     0

尝试的SQL及问题 #

我尝试了以下查询，但执行时一直卡顿无法结束：

SELECT CASE WHEN PB1.Title = SB.TITLE THEN
    CASE WHEN PB2.Pages = SB.PAGES THEN
        CASE WHEN SUBSTRING(CONVERT(varchar(15), PB3.DateOfRelease, 112), 1, 8) = SUBSTRING(CONVERT(varchar(15), SB.DATEOFRELEASE, 112), 1, 8) THEN
            '1'
        ELSE
            '0'
        END
    ELSE
        '0'
    END
ELSE
    '0'
END
AS STATUS
FROM SECONDARYBOOKS SB
LEFT JOIN PrimaryBooks PB1 ON PB1.Title = SB.TITLE
LEFT JOIN PrimaryBooks PB2 ON PB2.Pages = SB.PAGES
LEFT JOIN PrimaryBooks PB3 ON PB3.DateOfRelease = SB.DATEOFRELEASE
GROUP BY SB.COLOR, SB.PRICE, SB.TITLE, SB.PAGES, SB.DATEOFRELEASE, PB1.Title, PB2.Pages, PB3.DateOfRelease

原SQL卡顿原因 #

多次左连同一个PrimaryBooks表且仅用单个字段关联，会产生大量笛卡尔积：比如PB1匹配N条、PB2匹配M条、PB3匹配K条，最终生成NMK条中间记录，数据量暴增导致查询卡顿。同时嵌套CASE逻辑冗余，日期转换完全没必要（直接比较日期类型即可）。

正确的SQL实现 #

方法1：修改原表添加并更新status列（推荐） #

通过EXISTS子查询判断匹配关系，逻辑清晰且性能优异：

-- 先添加status列
ALTER TABLE SECONDARYBOOKS ADD STATUS INT;

-- 更新status值
UPDATE SECONDARYBOOKS SB
SET STATUS = CASE
    WHEN EXISTS (
        SELECT 1
        FROM PrimaryBooks PB
        WHERE PB.Title = SB.TITLE
          AND PB.Pages = SB.PAGES
          AND PB.DateOfRelease = SB.DATEOFRELEASE
    ) THEN 1
    ELSE 0
END;

-- 查看最终结果
SELECT * FROM SECONDARYBOOKS;

方法2：一次性查询结果（不修改原表） #

用一次左连关联所有匹配字段，再判断是否存在匹配记录：

SELECT
    SB.COLOR,
    SB.PRICE,
    SB.TITLE,
    SB.PAGES,
    SB.DATEOFRELEASE,
    CASE WHEN PB.Title IS NOT NULL THEN 1 ELSE 0 END AS STATUS
FROM SECONDARYBOOKS SB
LEFT JOIN PrimaryBooks PB
    ON PB.Title = SB.TITLE
    AND PB.Pages = SB.PAGES
    AND PB.DateOfRelease = SB.DATEOFRELEASE;

性能优化建议 #

• 为PrimaryBooks表的Title、Pages、DateOfRelease创建复合索引，加速匹配查询：

  CREATE INDEX IX_PrimaryBooks_Match ON PrimaryBooks (Title, Pages, DateOfRelease);
  

• 避免对日期字段做字符串转换，直接比较日期类型可利用索引，提升查询效率。

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