html - 如何修改此 SQL 查询中的 XML 代码以突出显示表的特定行？

Question

我正在使用 SQL Server 2012 并且我有以下 SQL 代码，它们输出 HTML 代码用于 Table。输出存储在 nvarchar (max) 变量中。

下面显示的是代码及其对应的输出(以 HTML 表格的形式；注意:真正的输出是一组 html 代码):

declare @tableHtml nvarchar(max)
declare @style nvarchar(50) = 'border-bottom:1px solid #7AC0DA'
declare @MyTable table
(
    StayYear nvarchar(10),
    PropertyCode nvarchar(10),
    Jan nvarchar(10),
    Feb nvarchar(10),
    Mar nvarchar(10),
    Apr nvarchar(10),
    May nvarchar(10),
    Jun nvarchar(10),
    Jul nvarchar(10),
    Aug nvarchar(10),
    Sep nvarchar(10),
    Oct nvarchar(10),
    Nov nvarchar(10),
    Dec nvarchar(10),
    Total nvarchar(50)
)
insert into @MyTable 
SELECT * FROM ITB   

select @tableHtml = (    

select
    '1' as '@border',
    '4' as '@cellpadding',
    'font-size:12px; font-family:Arial' as '@style',
    (
        select  (select @style as '@style', 'StayYear' as '*' for xml path('th'), type),
                (select @style as '@style', 'PropertyCode' as '*' for xml path('th'), type),
                (select @style as '@style', 'Jan' as '*' for xml path('th'), type),
                (select @style as '@style', 'Feb' as '*' for xml path('th'), type),
                (select @style as '@style', 'Mar' as '*' for xml path('th'), type),
                (select @style as '@style', 'Apr' as '*' for xml path('th'), type),
                (select @style as '@style', 'May' as '*' for xml path('th'), type),
                (select @style as '@style', 'Jun' as '*' for xml path('th'), type),
                (select @style as '@style', 'Jul' as '*' for xml path('th'), type),
                (select @style as '@style', 'Aug' as '*' for xml path('th'), type),
                (select @style as '@style', 'Sep' as '*' for xml path('th'), type),
                (select @style as '@style', 'Oct' as '*' for xml path('th'), type),
                (select @style as '@style', 'Nov' as '*' for xml path('th'), type),
                (select @style as '@style', 'Dec' as '*' for xml path('th'), type),
                (select @style as '@style', 'Total' as '*' for xml path('th'), type)


        for xml path('tr'), type
    ),
    (
        select 'trclass' as '@class',
                (select StayYear as '*' for xml path('td'), type),
                (select PropertyCode as '*' for xml path('td'), type),
                (select Jan as '*' for xml path('td'), type),
                (select Feb as '*' for xml path('td'), type),
                (select Mar as '*' for xml path('td'), type),
                (select Apr as '*' for xml path('td'), type),
                (select May as '*' for xml path('td'), type),
                (select Jun as '*' for xml path('td'), type),
                (select Jul as '*' for xml path('td'), type),
                (select Aug as '*' for xml path('td'), type),
                (select Sep as '*' for xml path('td'), type),
                (select Oct as '*' for xml path('td'), type),
                (select Nov as '*' for xml path('td'), type),
                (select Dec as '*' for xml path('td'), type),
                (select Total as '*' for xml path('td'), type)          

        from @MyTable
        GROUP BY [StayYear], [PropertyCode], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Total]
        ORDER BY  [PropertyCode], [StayYear] DESC
        for xml path('tr'), type
    )

for xml path('table')
)

select @tableHtml

输出是:

但是，我想要的输出如下:

为了实现这一点，我需要在我的 XML 代码中修改什么？基本上，我想对与每组 PropertyCode(即 A、B 和 C)关联的行的颜色值进行硬编码。

由于我不太熟悉 HTML 和 XML，所以我很难找到解决方案。

Answer 1

我们可以添加这一列-

(case when [PropertyCode]='A' then 'background-color:green' when [PropertyCode]='B' then 'background-color:blue' end) as '@style'

在此列之后(SQL 脚本的第 51 行)-

select 'trclass' as '@class'

我只为属性代码 A 和 B 添加了大小写。我们可以为其他属性代码添加相同的大小写。