如何将查询结果与tblStockistMaster表比对并提取STOCKIST NAME

我来帮你搞定这个需求！你现在需要把现有查询的结果和tblStockistMaster表关联，拿到对应的STOCKIST NAME对吧？其实很简单，咱们可以把你现有的查询作为一个临时结果集，再和tblStockistMaster做精准关联匹配就行。下面给你两种实现方式，选你觉得顺手的用：

方式一：使用CTE（可读性更好） #

CTE（公共表表达式）能让你的SQL逻辑更清晰，适合复杂查询场景：

WITH FilteredStockists AS (
    -- 这部分是你原来的查询，先筛选出符合条件的记录
    SELECT stoMast.sStockistCode, stoMast.sStateName, stoMast.sDivision, stateMap.sRMCode 
    FROM tblRSM_State_Mapping stateMap 
    INNER JOIN tblStockistMaster stoMast ON stateMap.sStateName = stoMast.sStateName 
    WHERE stateMap.sRMCode = 'MCNE04001' and stoMast.sDivision = 'CIDIS' 
    EXCEPT 
    SELECT sStockistCode, sStateName, sDivision, sRMCode From tblEntry
)
-- 关联tblStockistMaster获取STOCKIST NAME
SELECT 
    fs.sStockistCode,
    fs.sStateName,
    fs.sDivision,
    fs.sRMCode,
    sm.sStockistName  -- 注意：替换成你表中实际的STOCKIST NAME字段名
FROM FilteredStockists fs
INNER JOIN tblStockistMaster sm 
    ON fs.sStockistCode = sm.sStockistCode 
    AND fs.sStateName = sm.sStateName 
    AND fs.sDivision = sm.sDivision;

方式二：使用子查询 #

如果不习惯CTE，直接把子查询写在FROM里也可以：

SELECT 
    sub.sStockistCode,
    sub.sStateName,
    sub.sDivision,
    sub.sRMCode,
    sm.sStockistName  -- 替换成实际字段名
FROM (
    -- 原查询作为子查询
    SELECT stoMast.sStockistCode, stoMast.sStateName, stoMast.sDivision, stateMap.sRMCode 
    FROM tblRSM_State_Mapping stateMap 
    INNER JOIN tblStockistMaster stoMast ON stateMap.sStateName = stoMast.sStateName 
    WHERE stateMap.sRMCode = 'MCNE04001' and stoMast.sDivision = 'CIDIS' 
    EXCEPT 
    SELECT sStockistCode, sStateName, sDivision, sRMCode From tblEntry
) sub
INNER JOIN tblStockistMaster sm 
    ON sub.sStockistCode = sm.sStockistCode 
    AND sub.sStateName = sm.sStateName 
    AND sub.sDivision = sm.sDivision;

关键说明 #

• 我们用你原查询得到的sStockistCode、sStateName、sDivision三个字段作为关联条件，确保能精准匹配到tblStockistMaster里的对应记录。
• 一定要把代码里的sStockistName替换成你tblStockistMaster表中实际存储STOCKIST NAME的字段名哦！

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