問題描述

我在此 MSDN 頁面上使用類似于示例 C 的內容:http://msdn.microsoft.com/en-us/library/ms190307.aspx

I'm using something similar to example C on this MSDN page: http://msdn.microsoft.com/en-us/library/ms190307.aspx

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

我有一個名為 Rating 的列，根據我自己的邏輯設置為好"或壞".我想讓所有評級為壞"的行都有紅色背景.我知道如何在 HTML 中執行此操作，但不確定如何使用此示例中演示的FOR XML"查詢來執行此操作.似乎我必須為某些 TD 語句添加屬性，而不是其他語句.

I have a column called Rating that is set to 'Good' or 'Bad' according to my own logic. I would like to make all lines that have a rating of 'Bad' have a red background. I know how to do it in HTML, but not sure how to do it with the "FOR XML" query being demonstrated in this example. Seems like I would have to add an attribute to some TD statements, and not others.

推薦答案

你不能直接做.需要一點手工"HTML.這是一種可以使用的方法.

You can not do it directly.A little "Handcrafted" HTML is required. Here is an approach that can be used.

在單獨的 CTE 中選擇好記錄和壞記錄并附加td"標簽.壞的也附加樣式信息.

Select good and bad records in separate CTE and append the "td" tags. for bad ones append the Style information as-well.

然后附加tr"標簽并組合 (UNION) 數據行并使用 for xmlpath 連接它們.

Then append "tr" tags and combine (UNION) data rows and concatenate them using for xmlpath.

為簡單起見，我刪除了按列排序，但您可以在 CTE 中選擇它們并稍后對結果進行排序.

I have removed order by columns for simplicity but you can select them in the CTE and order the results later.

注意:我已經測試了輸出 HTML，它可以工作，但我不是 HTML 人，所以不要介意 HTML 標簽中是否有任何錯誤.請隨時更正.

Note: I have tested the output HTML and it works but I am not HTML guy so don't mind if there is any mistake in HTML tags.feel free to correct it.

DECLARE @tableHTML  NVARCHAR(MAX) 
,@Data NVARCHAR (MAX)=''
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' 

 ;WITH CTE_Good AS
 (
 SELECT          HTMLRowData=    N'<td>'+STR(wo.WorkOrderID)+N'</td>'
                        +N'<td>'+STR(p.ProductID)+N'</td>' 
                        +N'<td>'+p.Name+N'</td>' 
                        +N'<td>'+STR(wo.OrderQty)+N'</td>' 
                        +N'<td>'+CONVERT(VARCHAR(10),wo.DueDate,101)+N'</td>' 
                        +N'<td>'+STR((p.ListPrice - p.StandardCost) * wo.OrderQty)+N'</td>' 
      FROM AdventureWorks.Production.WorkOrder as wo
      JOIN AdventureWorks.Production.Product AS p
      ON wo.ProductID = p.ProductID
      WHERE DueDate > '2004-04-30'
        AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
        --AND Rating = 'Good'
  )
,CTE_Bad AS 
  (
   SELECT        HTMLRowData=    N'<td><p style="color:red">'+STR(wo.WorkOrderID)+N'</p></td>'
                        +N'<td><p style="color:red">'+STR(p.ProductID)+N'</p></td>' 
                        +N'<td><p style="color:red">'+p.Name+N'</p></td>' 
                        +N'<td><p style="color:red">'+STR(wo.OrderQty)+N'</p></td>'
                        +N'<td><p style="color:red">'+CONVERT(VARCHAR(10),wo.DueDate,101)+N'</p></td>' 
                        +N'<td><p style="color:red">'+STR((p.ListPrice - p.StandardCost) * wo.OrderQty)+N'</p></td>'
      FROM AdventureWorks.Production.WorkOrder as wo
      JOIN AdventureWorks.Production.Product AS p
      ON wo.ProductID = p.ProductID
      WHERE DueDate > '2004-04-30'
      AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
      --AND Rating = 'Bad'

  )

SELECT @Data=(SELECT  HTMLRows 
              FROM    (
        SELECT N'<tr>'+HTMLRowData+N'</tr>' AS HTMLRows FROM CTE_Good
        UNION  SELECT N'<tr>'+HTMLRowData+N'</tr>' AS HTMLRows FROM CTE_Bad 
       ) mi            
     FOR XML PATH(''), TYPE
       ).value('/', 'NVARCHAR(MAX)')

  SET @tableHTML=@tableHTML+@Data+N'</table>'

--SELECT @tableHTML

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;

這篇關于sp_send_dbmail - 將表格中的行格式化為紅色以發出警報的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！