update formtable_main_149 set spr=(SELECT SUBSTRING(STUFF( (SELECT DISTINCT ',' + a.lastname AS [text()] FROM (SELECT a.requestid,a.userid,b.lastname FROM workflow_currentoperator a JOIN hrmresource b ON a.userid=b.id WHERE a.requestid=$requestid$) a FOR XML PATH('')), 1, 1, ''), CHARINDEX(',', STUFF( (SELECT DISTINCT ',' + a.lastname AS [text()] FROM (SELECT a.requestid,a.userid,b.lastname FROM workflow_currentoperator a JOIN hrmresource b ON a.userid=b.id WHERE a.requestid=$requestid$) a FOR XML PATH('')), 1, 1, '')) + 1, LEN(STUFF( (SELECT DISTINCT ',' + a.lastname AS [text()] FROM (SELECT a.requestid,a.userid,b.lastname FROM workflow_currentoperator a JOIN hrmresource b ON a.userid=b.id WHERE a.requestid=$requestid$) a FOR XML PATH('')), 1, 1, ''))) as name) where requestid=$requestid$检查并简化这段sql

可以将这段 SQL 进行简化，如下所示：

UPDATE formtable_main_149
SET spr = (
    SELECT SUBSTRING(
        STUFF((
            SELECT DISTINCT ',' + b.lastname AS [text()]
            FROM workflow_currentoperator a
            JOIN hrmresource b ON a.userid = b.id
            WHERE a.requestid = $requestid$
            FOR XML PATH('')
        ), 1, 1, ''), 
        CHARINDEX(',', STUFF((
            SELECT DISTINCT ',' + b.lastname AS [text()]
            FROM workflow_currentoperator a
            JOIN hrmresource b ON a.userid = b.id
            WHERE a.requestid = $requestid$
            FOR XML PATH('')
        ), 1, 1, '')) + 1, 
        LEN(STUFF((
            SELECT DISTINCT ',' + b.lastname AS [text()]
            FROM workflow_currentoperator a
            JOIN hrmresource b ON a.userid = b.id
            WHERE a.requestid = $requestid$
            FOR XML PATH('')
        ), 1, 1, '')))
    ) AS name
)
WHERE requestid = $requestid$;

主要的改动包括：

- 将查询中重复的部分提取出来，避免多次书写。
- 将 SELECT 语句中的表别名 a 修改为更有意义的 b。
- 格式化代码使其更易读。