如何在SQL生成的HTML邮件表格中合并同值Group ID与合计列单元格

我通过以下SQL生成HTML表格用于邮件发送，希望实现仅当Group ID列和Total Transaction Sum列的值完全相同时，合并对应单元格，以此提升表格可读性。

现有SQL代码 #

CREATE TABLE #list (GroupID int,AccountID int,Country varchar (20),AccountTransactionSum int)

Insert into #list
values 
(1,18754,'United Kingdom',110),
(1,24865,'Germany',265),
(1,82456,'Poland',1445),
(1,98668,'United Kingdom',60),
(1,37843,'France',1490),
(2,97348,'United Kingdom',770)

DECLARE @xmlBody      XML   
SET @xmlBody = (SELECT (SELECT  GroupID,                        AccountID,                      Country,            AccountTransactionSum,          TotalTransactionSum = sum(AccountTransactionSum) over (partition by GroupID)
                        FROM #list
                        ORDER BY GroupID 
                        FOR XML PATH('row'), TYPE, ROOT('root')).query('<html><head><meta charset="utf-8"/><style>
                                                                            table <![CDATA[ {border-collapse: collapse; } ]]>
                                                                            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
                                                                            th, td <![CDATA[ { text-align: center; padding: 8px;} ]]>
                                                                            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
                                                                            </style></head>
                                                                            <body><table border="1" cellpadding="10" style="border-collapse:collapse;">
                                                                            <thead><tr>
                                                                            <th>No.</th>
                                                                            <th> Group ID </th><th> Account ID </th><th> Country </th><th> Account Transaction Sum </th><th> Total Transaction Sum </th>
                                                                            </tr></thead>
                                                                            <tbody>
                                                                            {for $row in /root/row
                                                                            let $pos := count(root/row[. << $row]) + 1
                                                                            return <tr align="center" valign="center">
                                                                            <td>{$pos}</td>
                                                                            <td>{data($row/GroupID)}</td><td>{data($row/AccountID)}</td><td>{data($row/Country)}</td><td>{data($row/AccountTransactionSum)}</td><td>{data($row/TotalTransactionSum)}</td>
                                                                            </tr>}
                                                                            </tbody></table></body></html>'));

    
select @xmlBody

当前表格效果 #

期望表格效果 #

HTML编辑器地址 #

https://codebeautify.org/real-time-html-editor/y237bf87d

解决方案 #

要实现单元格合并，需要在XML查询中通过XPath判断当前行与同组内首行的关系，仅在组内首行输出GroupID和TotalTransactionSum单元格并设置rowspan，组内其余行则跳过这两个单元格的内容。修改后的SQL代码如下：

CREATE TABLE #list (GroupID int,AccountID int,Country varchar (20),AccountTransactionSum int)

Insert into #list
values 
(1,18754,'United Kingdom',110),
(1,24865,'Germany',265),
(1,82456,'Poland',1445),
(1,98668,'United Kingdom',60),
(1,37843,'France',1490),
(2,97348,'United Kingdom',770)

DECLARE @xmlBody XML   
SET @xmlBody = (SELECT (SELECT 
                          GroupID,
                          AccountID,
                          Country,
                          AccountTransactionSum,
                          TotalTransactionSum = SUM(AccountTransactionSum) OVER (PARTITION BY GroupID),
                          -- 计算当前组内的行数，用于设置rowspan
                          GroupRowCount = COUNT(*) OVER (PARTITION BY GroupID),
                          -- 标记是否为组内第一行
                          IsFirstInGroup = CASE WHEN ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY AccountID) = 1 THEN 1 ELSE 0 END
                        FROM #list
                        ORDER BY GroupID, AccountID
                        FOR XML PATH('row'), TYPE, ROOT('root')).query('<html><head><meta charset="utf-8"/><style>
                                                                            table <![CDATA[ {border-collapse: collapse; } ]]>
                                                                            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
                                                                            th, td <![CDATA[ { text-align: center; padding: 8px;} ]]>
                                                                            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
                                                                            </style></head>
                                                                            <body><table border="1" cellpadding="10" style="border-collapse:collapse;">
                                                                            <thead><tr>
                                                                            <th>No.</th>
                                                                            <th> Group ID </th><th> Account ID </th><th> Country </th><th> Account Transaction Sum </th><th> Total Transaction Sum </th>
                                                                            </tr></thead>
                                                                            <tbody>
                                                                            {for $row in /root/row
                                                                            let $pos := count(root/row[. << $row]) + 1
                                                                            return <tr align="center" valign="center">
                                                                            <td>{$pos}</td>
                                                                            <!-- 仅组内第一行输出GroupID单元格并设置rowspan -->
                                                                            {if ($row/IsFirstInGroup = 1) then 
                                                                                &lt;td rowspan=&quot;{data($row/GroupRowCount)}&quot;&gt;{data($row/GroupID)}&lt;/td&gt; 
                                                                             else ()
                                                                            }
                                                                            &lt;td&gt;{data($row/AccountID)}&lt;/td&gt;
                                                                            &lt;td&gt;{data($row/Country)}&lt;/td&gt;
                                                                            &lt;td&gt;{data($row/AccountTransactionSum)}&lt;/td&gt;
                                                                            <!-- 仅组内第一行输出TotalTransactionSum单元格并设置rowspan -->
                                                                            {if ($row/IsFirstInGroup = 1) then 
                                                                                &lt;td rowspan=&quot;{data($row/GroupRowCount)}&quot;&gt;{data($row/TotalTransactionSum)}&lt;/td&gt; 
                                                                             else ()
                                                                            }
                                                                            &lt;/tr&gt;}
                                                                            &lt;/tbody&gt;&lt;/table&gt;&lt;/body&gt;&lt;/html&gt;'));

SELECT @xmlBody

修改说明 #

1. 在查询中新增两个计算列：
   • GroupRowCount：统计每个GroupID对应的行数，用于设置单元格的rowspan属性值
   • IsFirstInGroup：标记当前行是否为组内第一行（通过ROW_NUMBER()实现）
2. 在XML的XQuery逻辑中，仅当IsFirstInGroup为1时，才输出GroupID和TotalTransactionSum单元格，并设置rowspan为对应组的行数；组内其余行则跳过这两个单元格的输出，从而实现合并效果。

内容的提问来源于stack exchange，提问作者Luna