关于SQL Server中STUFF的用法和基本优化

是在工作中有一个需求如下：
           在已经查出的数据A表中插入一个字段结果 统计B表中的某个字段并且用逗号隔开。
例如这个sql：
 

SELECT *,
       STUFF((select CASE
                         WHEN JH_HWZF_ZFD.CYFPH IS NOT NULL AND JH_HWZF_ZFD.CYFPH != ''
                             THEN ', ' + JH_HWZF_ZFD.CYFPH
                         ELSE '' end
              FROM  JH_HWZF_ZFD
              WHERE JH_HWZF_ZFD.PIH = MX.PIH
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') AS CYFPH
FROM JH_HWZF_DZD AS DZD WITH (NOLOCK)
         LEFT JOIN JH_HWZF_DZDMX AS MX WITH (NOLOCK) ON DZD.DZDH = MX.DZDH AND MX.SCBJ = 0
         left join JH_HWZF_SQD as SQD with (nolock) on MX.FKSQDH = SQD.SQDH and sqd.SCBJ = 0
WHERE DZD.SCBJ = 0

这里使用的STUFF函数将查询结果使用逗号隔开，具体STUFF函数可以找其他文章看一下使用方法。
但是这里的子查询是写在SELECT语句中的，数据量越大，查询次数越多，速度越慢，实际测试数据在不到三千条的时候，就已经需要六十多秒的时间才能查完；

优化后可以声明一个临时变量表，使STUFF中的查询到临时变量表中去查，这样速度会提升非常快。

声明一个变量表 @zfd 里面是表的字段和类型

   DECLARE @zfd TABLE
        (
        PIH    VARCHAR(255),
        FPZDBH VARCHAR(255),
        CGS    VARCHAR(255),
        FPRZRQ date,
        CYFS   VARCHAR(255),
        CYFPH  VARCHAR(255),
        HWDCRQ date,
        HWRKDH VARCHAR(255)
        );

这里是将临时要用到的数据查询出来 赋值给上方的临时变量表，这个尽量缩小表的范围和大小
 

        INSERT INTO @zfd
        SELECT PIH, FPZDBH, CGS, FPRZRQ, CYFS, CYFPH, HWDCRQ,HWRKDH
        FROM JH_HWZF_ZFDMX with (nolock)
        right join JH_HWZF_ZFD with (nolock) on JH_HWZF_ZFDMX.ZFDH = JH_HWZF_ZFD.ZFDH
        WHERE JH_HWZF_ZFD.SCBJ = 0
        and JH_HWZF_ZFDMX.SCBJ = 0
        and PIH in (

            SELECT
        mx.PIH
        FROM JH_HWZF_DZD AS DZD WITH (NOLOCK)
        LEFT JOIN JH_HWZF_DZDMX AS MX WITH (NOLOCK) ON DZD.DZDH = MX.DZDH AND MX.SCBJ = 0
        left join JH_HWZF_SQD as SQD with (nolock) on MX.FKSQDH = SQD.SQDH and sqd.SCBJ = 0
        WHERE DZD.SCBJ = 0

 下面的子查询中，需要取得数据从临时变量表中取值即可，实测目前查询是一秒多

SELECT *,
       STUFF((select CASE
                         WHEN JH_HWZF_ZFD.CYFPH IS NOT NULL AND JH_HWZF_ZFD.CYFPH != ''
                             THEN ', ' + JH_HWZF_ZFD.CYFPH
                         ELSE '' end
              FROM @zfd as JH_HWZF_ZFD
              WHERE JH_HWZF_ZFD.PIH = MX.PIH
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') AS CYFPH
FROM JH_HWZF_DZD AS DZD WITH (NOLOCK)
         LEFT JOIN JH_HWZF_DZDMX AS MX WITH (NOLOCK) ON DZD.DZDH = MX.DZDH AND MX.SCBJ = 0
         left join JH_HWZF_SQD as SQD with (nolock) on MX.FKSQDH = SQD.SQDH and sqd.SCBJ = 0
WHERE DZD.SCBJ = 0

下面是完整的sql

不需要分开写  完整的执行就行

DECLARE @zfd TABLE
             (
                 PIH    VARCHAR(255),
                 FPZDBH VARCHAR(255),
                 CGS    VARCHAR(255),
                 FPRZRQ date,
                 CYFS   VARCHAR(255),
                 CYFPH  VARCHAR(255),
                 HWDCRQ date,
                 HWRKDH VARCHAR(255)
             );


INSERT INTO @zfd
SELECT PIH, FPZDBH, CGS, FPRZRQ, CYFS, CYFPH, HWDCRQ,HWRKDH
FROM JH_HWZF_ZFDMX with (nolock)
         right join JH_HWZF_ZFD with (nolock) on JH_HWZF_ZFDMX.ZFDH = JH_HWZF_ZFD.ZFDH
WHERE JH_HWZF_ZFD.SCBJ = 0
  and JH_HWZF_ZFDMX.SCBJ = 0;



SELECT *,
       STUFF((select CASE
                         WHEN JH_HWZF_ZFD.CYFPH IS NOT NULL AND JH_HWZF_ZFD.CYFPH != ''
                             THEN ', ' + JH_HWZF_ZFD.CYFPH
                         ELSE '' end
              FROM @zfd as JH_HWZF_ZFD
              WHERE JH_HWZF_ZFD.PIH = MX.PIH
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') AS CYFPH
FROM JH_HWZF_DZD AS DZD WITH (NOLOCK)
         LEFT JOIN JH_HWZF_DZDMX AS MX WITH (NOLOCK) ON DZD.DZDH = MX.DZDH AND MX.SCBJ = 0
         left join JH_HWZF_SQD as SQD with (nolock) on MX.FKSQDH = SQD.SQDH and sqd.SCBJ = 0
WHERE DZD.SCBJ = 0