我如何利用我在VBScript中的SQL字段发送电子邮件

Question

以下是我的SQL脚本，在我需要在电子邮件主体中显示名称，电子邮件和项目名称。

SELECT 
    name=p1.user_id,
    address=p1.user_email_addr,
    ( SELECT proj_nm + ', ' 
      FROM (
             select distinct 
                    user_id,
                    user_email_addr,
                    proj_nm 
             from test_table1 
             where user_enabled='Y' 
                   and user_extrl <> 'Y'
                   and datediff(dd,user_ts,getdate()) between 200 and 215 
                   and proj_src_nm <>'testproject1'
                   and proj_src_nm <>'testproject2'
                   and user_id <>'D_N_E') p2 
     WHERE p2.user_id = p1.user_id 
     ORDER BY proj_nm FOR XML PATH('')
   ) AS project 
FROM (  select distinct 
               user_id,
               user_email_addr,
               proj_nm 
        from test_table1 
        where user_enabled='Y' 
              and user_extrl <> 'Y'
              and datediff(dd,user_ts,getdate()) between 200 and 215 
              and proj_src_nm <>'testproject1'
              and proj_src_nm <>'testproject2'
              and user_id <>'D_N_E') p1 
GROUP BY user_id,user_email_addr;

在我的vbscript文件中，我包含以下代码行。

SendMail recordset("name"), recordset("address"), recordset("project")
objMessage.HTMLBody = strTable1 & vbCrLf & vbCrLf & strTable2 & vbCrLf & vbCrLf & strTable3 

strtable1将显示名称，strtable2显示地址，strtable3以显示从SQL显示项目。名称和地址字段都在起作用，但是项目字段不起作用。

Answer 1

看起来您尝试使用此处描述的FOR XML方法的一部分来汇总字符串，但错过了其工作原理 - 分组是由 ofter 查询执行的。该子查询将所有与组键匹配的行并将结果返回为XML，将空字符串用作元素名称，从而产生一个长字符串：

SELECT Name, Pets = STUFF((SELECT N', ' + Pet 
    FROM dbo.FamilyMemberPets AS p2
    WHERE p2.name = p.name 
    ORDER BY Pet
    FOR XML PATH(N'')), 1, 2, N'')
FROM dbo.FamilyMemberPets AS p
GROUP BY Name
ORDER BY Name;

您不必重复 concregation suqbquery中的查询。仅将其与组密钥相关联。分组时，您不必使用DISTINCT。

经过大量简化后，查询将成为：

SELECT 
    name=p1.user_id,
    address=p1.user_email_addr,
    Project=STUFF(( SELECT distinct N', ' + proj_nm
                    from test_table1 as p2
                    where p2.user_id=p1.user_id and 
                          p2.user_email_addr=p1.user_email_addr
                    order by proj_nm
                    FOR XML PATH(N'')), 1, 2, N'') 
FROM test_table1 p1
where user_enabled='Y' 
      and user_extrl <> 'Y'
      and user_ts between dateadd(dd,-215,getdate()) and dateadd(dd,-200,getdate())
      and proj_src_nm <>'testproject1'
      and proj_src_nm <>'testproject2'
      and user_id <>'D_N_E'
GROUP BY user_id,user_email_addr;

请注意：

的更改

and user_ts between dateadd(dd,-215,getdate()) and dateadd(dd,-200,getdate())

当您将功能应用于字段时，您无法使用任何覆盖函数来加快查询的索引。最好是反向该功能，以便user_ts在过去的两个日期之间。

在SQL Server 2017中，您可以使用String_agg函数来汇总字符串：

SELECT 
    name=p1.user_id,
    address=p1.user_email_addr,
    Project=STRING_AGG(proj_nm, ',')
FROM test_table1 p1
where user_enabled='Y' 
      and user_extrl <> 'Y'
      and user_ts between dateadd(dd,-215,getdate()) and dateadd(dd,-200,getdate())
      and proj_src_nm <>'testproject1'
      and proj_src_nm <>'testproject2'
      and user_id <>'D_N_E'
GROUP BY user_id,user_email_addr;