我有发送 HTML 表格电子邮件的工作代码，如果它们低于 0，我只需要帮助在 HTML 代码中对特定结果进行颜色编码。
set datefirst 1;
set nocount on;
if (object_id('tempdb..#temp') iS NOT NULL)
drop table #temp
declare @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);
USE PL00
select w.WeekDayID,w.[WeekDay] + FORMAT(convert(date,convert(varchar(10),dateadd(dd,w.WeekDayID-DATEPART(dw,getdate()),GETDATE()),121)),' (MM/dd)') as [WeekDay]
,SUM(CASE o.[TYPE] WHEN 'SVC' THEN o.SUB_TOTAL ELSE 0.0 END) as TotalSales,COUNT(DISTINCT CASE o.[TYPE] WHEN 'SVC' THEN o.ORDER_NO ELSE NULL END) as TotalOrders
,MAX(CASE o.[TYPE] WHEN 'SVC' THEN e.U_DLYSALES ELSE 0.0 END) as SalesGoal
into #temp
from (select 1 as WeekDayID,'Monday' as [WeekDay] union select 2,'Tuesday' union select 3,'Wednesday' union select 4,'Thursday' union select 5,'Friday' union select 6,'Saturday' union select 7,'Sunday') as w
left join schedule s on w.WeekDayID = DATEPART(dw,s.[DATE]) inner join ordhdr o on s.ID_VAL = o.ORDER_NO left join employee e on o.leadtech = e.emp_id
where s.[DATE] between convert(datetime,convert(varchar(10),dateadd(dd,1-DATEPART(dw,getdate()),GETDATE()),121)) and convert(datetime,convert(varchar(10), GETDATE(),121))
-- and o.[TYPE] = 'SVC'
and e.EMP_ID = '45-ANGORTI'
group by w.WeekDayID,w.[WeekDay]
union
select 8 as WeekDayID,'Weekly' as [WeekDay], SUM(o2.SUB_TOTAL) as TotalSales,COUNT(DISTINCT o2.ORDER_NO) as TotalOrders,MAX(ISNULL(e.U_WKSALES,0.0)) as SalesGoal
from ordhdr o2 inner join schedule s2 on s2.ID_VAL = o2.ORDER_NO left join employee e on o2.leadtech = e.emp_id
where s2.[DATE] between convert(datetime,convert(varchar(10),dateadd(dd,1-DATEPART(dw,getdate()),GETDATE()),121))
and convert(datetime,convert(varchar(10),GETDATE(),121))
and o2.[TYPE] = 'SVC'
and e.EMP_ID = '45-ANGORTI'
union
select 9 as WeekDayID,'Monthly' as [WeekDay], SUM(o2.SUB_TOTAL) as TotalSales,COUNT(DISTINCT o2.ORDER_NO) as TotalOrders,MAX(ISNULL(e.U_MONSALES,0.0)) as SalesGoal
from ordhdr o2 inner join schedule s2 on s2.ID_VAL = o2.ORDER_NO left join employee e on o2.leadtech = e.emp_id
where s2.[DATE] between convert(datetime,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
and convert(datetime,convert(varchar(10),GETDATE(),121))
and o2.[TYPE] = 'SVC'
and e.EMP_ID = '45-ANGORTI'
insert #temp
select
w.WeekDayID, w.[WeekDay] + FORMAT(convert(date,convert(varchar(10),dateadd(dd,w.WeekDayID-DATEPART(dw,getdate()),GETDATE()),121)),' (MM/dd)') as [WeekDay]
,0.0 as TotalSales,0 as TotalOrders,0.0 as SalesGoal
from (select 1 as WeekDayID,'Monday' as [WeekDay] union select 2,'Tuesday' union select 3,'Wednesday' union select 4,'Thursday' union select 5,'Friday' union select 6,'Saturday' union select 7,'Sunday') as w
left join #temp t on w.WeekDayID = t.WeekDayID
where t.WeekDayID is null
--delete #temp where WeekDayID >= DATEPART(dw,getdate())
--select e.U_DLYSALES,e.U_WKSALES,e.U_MONSALES, * from employee e where e.U_DLYSALES > 0.0
select * from #temp order by WeekDayID
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT #temp.[WeekDay] AS 'td','', #temp.TotalSales AS 'td','',#temp.TotalOrders AS 'td','',#temp.SalesGoal AS 'td', '',
(#temp.TotalSales - #temp.SalesGoal) AS 'td'
from #temp
order by WeekDayID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3> TEST. </H3>
<table border = 1>
<tr>
<th> Weekday </th> <th> TotalSales </th> <th> Total ORders </th> <th> SalesGoal </th> <th> Difference </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mail.pacificlawnsprinklers.com',
@body = @body,
@body_format ='HTML',
@recipients = 'Mmahoney@pacificlawnsprinklers.com', -- replace with your email address
@subject = 'TEST' ;
测试。
+-------------------+-------------+---------------+------------+-------------+
| Weekday | TotalSales | Total ORders | SalesGoal | Difference |
+-------------------+-------------+---------------+------------+-------------+
| Monday (08/05) | 2570.00 | 1 | 1000.00 | 1570.00 |
| Tuesday (08/06) | 1072.00 | 6 | 1000.00 | 72.00 |
| Wednesday (08/07) | 0.00 | 0 | 0.00 | 0.00 |
| Thursday (08/08) | 0.00 | 0 | 0.00 | 0.00 |
| Friday (08/09) | 0.00 | 0 | 0.00 | 0.00 |
| Saturday (08/10) | 0.00 | 0 | 0.00 | 0.00 |
| Sunday (08/11) | 0.00 | 0 | 0.00 | 0.00 |
| Weekly | 3642.00 | 7 | 6000.00 | -2358.00 |
| Monthly | 6244.00 | 13 | 25000.00 | -18756.00 |
+-------------------+-------------+---------------+------------+-------------+
您可以检查零值并将其包装在一个范围中并为其指定颜色。
例：
<span style="font-color:#f00">0</span>
由于您是针对电子邮件执行此操作，因此唯一的全局支持是在元素级别或数据级别添加内联颜色。
您可以添加类，但某些电子邮件客户端不会在头部读取CSS或将其剥离。如果你想走上课的路线，那么以下是你可以做的。
.red{color:#f00;}<span class="red">0</span>