如何修改SQL查询按匹配tocid填充所有行Site字段NULL值 存储过程优化

你可以通过窗口聚合函数按tocid分组提取对应Site值，替换原查询中的Site字段逻辑即可，修改后的完整语句如下：

SELECT        a.tocid,
              a.prop_id, 
              b.prop_name,
              a.str_val,
              -- 替换原来的CASE逻辑，按tocid分组取对应prop_id=57的str_val填充所有行
              MAX(CASE WHEN a.prop_id = '57' THEN a.str_val END) OVER (PARTITION BY a.tocid) as Site 
FROM            LaserRepository.dbo.toc AS d INNER JOIN
                         LaserRepository.dbo.propset AS c ON d.pset_id = c.pset_id INNER JOIN
                         LaserRepository.dbo.propval AS a INNER JOIN
                         LaserRepository.dbo.propdef AS b ON a.prop_id = b.prop_id ON d.tocid = a.tocid 
WHERE        (b.prop_id IN (59, 57, 127, 76, 86, 40, 686, 75, 637, 199, 85, 125, 128, 99, 675, 82, 688, 689)) 
AND (c.pset_id IN (4, 17, 24, 16, 7, 11)) 
AND c.pset_name = @template 
END

改动说明 #

• 原逻辑仅当前行prop_id = '57'时才会返回str_val作为Site值，其余行必然返回NULL
• 新增的OVER (PARTITION BY a.tocid)会按tocid对所有行分组，每组内统一提取prop_id = '57'对应的str_val，赋值给该组所有行，天然消除NULL值

如果你的数据库版本不支持窗口函数，也可以用子查询关联的方式实现：

WITH SiteMap AS (
    SELECT tocid, str_val AS Site
    FROM LaserRepository.dbo.propval
    WHERE prop_id = '57'
)
SELECT        a.tocid,
              a.prop_id, 
              b.prop_name,
              a.str_val,
              s.Site
FROM            LaserRepository.dbo.toc AS d INNER JOIN
                         LaserRepository.dbo.propset AS c ON d.pset_id = c.pset_id INNER JOIN
                         LaserRepository.dbo.propval AS a INNER JOIN
                         LaserRepository.dbo.propdef AS b ON a.prop_id = b.prop_id ON d.tocid = a.tocid
INNER JOIN SiteMap s ON a.tocid = s.tocid
WHERE        (b.prop_id IN (59, 57, 127, 76, 86, 40, 686, 75, 637, 199, 85, 125, 128, 99, 675, 82, 688, 689)) 
AND (c.pset_id IN (4, 17, 24, 16, 7, 11)) 
AND c.pset_name = @template 
END

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