基于Table2数据更新Table1的Score列（求和计算）SQL实现问询

嘿，我来帮你搞定这个Score列的更新任务，先把整个场景和解决方案理清楚：

我们有两个临时表#Table1和#Table2，需要根据#Table2定义的问题分值规则，计算#Table1中每条记录的回答对应总分，更新到Score列。预期结果是joe的Score为7，amy的Score为5。

表结构与初始数据 #

首先是两个临时表的创建和初始数据插入SQL：

-- 创建#Table1临时表
CREATE TABLE #Table1 (
    PK_Table1 INT PRIMARY KEY,
    Name VARCHAR(50),
    DoYouGoToSchool VARCHAR(3),
    DoYouhaveACar VARCHAR(3),
    DoYouWorkFullTime VARCHAR(3),
    DoYouWorkPartTime VARCHAR(3),
    Score INT
);

-- 插入#Table1初始数据
INSERT INTO #Table1 (PK_Table1, Name, DoYouGoToSchool, DoYouhaveACar, DoYouWorkFullTime, DoYouWorkPartTime, Score)
VALUES 
(1, 'joe', 'Yes', 'Yes', 'No', 'Yes', 0),
(2, 'amy', 'No', 'Yes', 'Yes', 'No', 0);

-- 创建#Table2临时表
CREATE TABLE #Table2 (
    Pk_Table2 INT PRIMARY KEY,
    Question VARCHAR(50),
    Answer BIT,
    Value INT
);

-- 插入#Table2初始数据
INSERT INTO #Table2 (Pk_Table2, Question, Answer, Value)
VALUES 
(1, 'DoYouGoToSchool', 1, 3),
(2, 'DoYouhaveACar', 1, 2),
(3, 'DoYouWorkFullTime', 1, 4),
(4, 'DoYouWorkPartTime', 1, 2);

完整Score更新SQL实现 #

这里我们用UNPIVOT把#Table1的多列回答转换为行数据，再和#Table2匹配计算总分，最后更新回原表。具体SQL如下：

-- 计算每个用户的总分并更新#Table1的Score列
UPDATE t1
SET t1.Score = calc.TotalScore
FROM #Table1 t1
JOIN (
    SELECT 
        PK_Table1,
        SUM(t2.Value) AS TotalScore
    FROM #Table1
    UNPIVOT (
        AnswerValue FOR Question IN (DoYouGoToSchool, DoYouhaveACar, DoYouWorkFullTime, DoYouWorkPartTime)
    ) up
    JOIN #Table2 t2 ON up.Question = t2.Question 
                    AND (up.AnswerValue = 'Yes' AND t2.Answer = 1)
    GROUP BY PK_Table1
) calc ON t1.PK_Table1 = calc.PK_Table1;

-- 验证结果
SELECT * FROM #Table1;

逻辑说明 #

1. UNPIVOT转换：把#Table1中每个用户的多个回答列（比如DoYouGoToSchool、DoYouhaveACar等）转换成多行数据，每行对应一个问题和用户的回答。
2. 匹配分值规则：将转换后的行数据和#Table2关联，匹配问题名称，并且用户回答为Yes时对应#Table2的Answer=1（True）。
3. 分组求和：按用户主键分组，求和符合条件的Value得到总分。
4. 更新回原表：把计算出的总分更新到#Table1的Score列。

执行完上面的SQL后，查询#Table1就能看到预期结果：joe的Score是7（3+2+2），amy的Score是5（2+4）。

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