mysql - 如何在一封电子邮件中创建多个 HTML 数据表

Question

我正在尝试在一封电子邮件中发送 2 个不同的表格。我希望一个表用于建立有关第一个数据库中存储的数据的数据，另一个表用于第二个数据库的信息。我可以简单地生成一封电子邮件，但我无法理解如何在同一封电子邮件中使用完全不同的查询和数据设置第二个表。

这是我目前正在处理的内容:

USE MY DATABASE

DECLARE 
@tableHTML NVARCHAR(MAX)




SET @tableHTML =
N'<H1>Lacrosse Inquiries</H1>' +
N'<table border="1" style="text-align: middle;" width:100% height:20px style="cell"      cellspacing="0" cellpadding="5">' +
N'<tr><th>FIRST NAME</th><th>LAST NAME</th><th>Entry Term</th></th><th>Market     Segment</th><th>State</th><th>Home Number</th><th>Cell Phone</th>'+
CAST ( ( SELECT td =  INQUIRY.F_NAME      , ''
            , td = INQUIRY.L_NAME    , ''
            , td = INQUIRY.ENTRY_TERM   , ''
            , td = INQUIRY.MARKET_SEG   , ''
            , td = EWADDRESS.STATE   , ''
            , td = EWADDRESS.HOME_PHONE   , ''
            , td = EWADDRESS.WORK_PHONE   , ''





from  EWSTUDENT AS INQUIRY inner join EWMULTI1M as sport on     sport.EWSTUID=INQUIRY.EWSTUID left JOIN EWADDRESS ON EWADDRESS.EWSTUID=INQUIRY.EWSTUID LEFT     JOIN ESTUDENT ON ESTUDENT.EWSTUID=INQUIRY.EWSTUID
where VCODETYPE = 'EACTIV1M' AND VCODE ='CLAX' AND INQUIRY.GENDER = 'F' AND STUD_TYPE     ='P' and (CONVERT(VARCHAR(8), sport.CREATE_DT, 112) = CONVERT(varchar(8), GETDATE(), 112))
order by INQUIRY.L_NAME




         FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 
        @profile_name= 'Jordan.Sorensen',
        @recipients = 'Jordan.sorensen@svu.edu',
        @from_address = 'Jordan.Sorensen@svu.edu',
        @body = @tableHTML,
        @execute_query_database= 'MY DATABASE',
        @subject ='Womens Lacrosse Inquiries',
        @body_format= 'HTML'

GO

关于如何在一封电子邮件中使用两组完全不同的数据创建两个不同的表有什么建议吗？

Answer 1

         FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>'          --<-- This is where you 1st query ends add a couple of line breaks
  + N'<br/><br/>'       -- and add you second table just like you have done before

  + N'<table border="1" style="text-align: middle;" width:100% height:20px style="cell" cellspacing="0" cellpadding="5">' 
  + N'<tr><th>FIRST NAME</th><th>LAST NAME</th><th>Entry Term</th></th><th>Market Segment</th><th>State</th><th>Home Number</th><th>Cell Phone</th>'
  + CAST ( (/*Your Select just like you have done for 1st table*/) AS NVARCHAR(MAX))
  + N'</table>'

显然，从两个sql查询生成的HTML被连接起来并将值设置为变量@tableHTML，其余的内容保持不变，将参数传递给您的过程等等等