SQL Server LocalDB跨表比对双值更新RESULT列实现方案

• 数据库：SQL Server LocalDB
• 涉及表：TABLE_1、TABLE_2

为TABLE_1的RESULT列整列赋值1或0，赋值前需要先关联TABLE_2获取两个虚拟计算字段：

• COL_LEFT_RANK：TABLE_1.COL_LEFT匹配TABLE_2.COL时对应的RANK值
• COL_RIGHT_RANK：TABLE_1.COL_RIGHT匹配TABLE_2.COL时对应的RANK值

赋值规则如下：

IF (PRICE_LEFT > PRICE_RIGHT AND COL_LEFT_RANK > COL_RIGHT_RANK)
OR (PRICE_LEFT < PRICE_RIGHT AND COL_LEFT_RANK < COL_RIGHT_RANK)
THEN RESULT = 1
ELSE RESULT = 0

逻辑运行效果示例：

附测试用表结构与初始化DDL：

CREATE TABLE TABLE_1 (
    COL_LEFT varchar(255),
    COL_RIGHT varchar(255),
    PRICE_LEFT int,
    PRICE_RIGHT int
);

CREATE TABLE TABLE_2 (
    COL varchar(255),
    RANK int
);


INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'G', 22, 4);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('C', 'A', 15, 14);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'D', 5, 18);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('A', 'F', 2, 2);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('F', 'E', 4, 8);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('G', 'C', 16, 6);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('D', 'C', 22, 28);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('A', 'G', 14, 19);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('F', 'D', 3, 12);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'A', 11, 9);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('D', 'F', 8, 2);
INSERT INTO TABLE_1 (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'F', 4, 1);

INSERT INTO TABLE_2 (COL, RANK) VALUES ('A', 5);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('B', 3);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('C', 1);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('D', 7);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('E', 6);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('F', 2);
INSERT INTO TABLE_2 (COL, RANK) VALUES ('G', 4);

1. 首先给TABLE_1添加RESULT列，如果该列已存在可跳过此步：

ALTER TABLE TABLE_1 ADD RESULT INT;

2. 执行更新语句，通过两次关联TABLE_2分别取左右两侧的排名值，按规则计算结果赋值：

UPDATE t1
SET t1.RESULT = CASE
    WHEN (t1.PRICE_LEFT > t1.PRICE_RIGHT AND t2_left.RANK > t2_right.RANK)
      OR (t1.PRICE_LEFT < t1.PRICE_RIGHT AND t2_left.RANK < t2_right.RANK)
    THEN 1
    ELSE 0
END
FROM TABLE_1 t1
-- 关联取COL_LEFT对应的排名
LEFT JOIN TABLE_2 t2_left ON t1.COL_LEFT = t2_left.COL
-- 关联取COL_RIGHT对应的排名
LEFT JOIN TABLE_2 t2_right ON t1.COL_RIGHT = t2_right.COL;

执行以下查询即可查看更新后的结果，可对照示例图校验正确性：

SELECT * FROM TABLE_1;

说明：当PRICE_LEFT = PRICE_RIGHT、左右排名相等、或者关联不到匹配的COL值时，不满足判断条件，RESULT统一赋值为0，与规则要求一致。

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