SQL中的HTML表格合并单元格-PHP中文网问答

我正在使用此查询创建 HTML 表并通过电子邮件发送。仅当“组 ID”和“总交易总和”列具有相同值时，是否可以合并单元格以提高可读性？下面是我想要得到的结果

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

SELECT GroupID, AccountID, Country, AccountTransactionSum, TotalTransactionSum = sum(AccountTransactionSum) over (partition by GroupID), rowspan = COUNT(*) OVER(PARTITION BY GroupID), display = CASE WHEN lag(GroupID) OVER(ORDER BY GroupID,AccountID) = GroupID THEN 'display:none' ELSE '' END FROM #list

DROP TABLE #list go SELECT * INTO #list FROM ( 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) ) t (groupid,accountid, country, AccountTransactionSum) DECLARE @xmlBody XML SET @xmlBody = (SELECT (SELECT GroupID, AccountID, Country, AccountTransactionSum, TotalTransactionSum = sum(AccountTransactionSum) OVER (partition BY GroupID), COUNT(*) OVER(PARTITION BY GroupID) AS rowspan, CASE WHEN lag(GroupID) OVER(ORDER BY groupid,accountid) = GroupID THEN 1 ELSE 0 END AS skipTd FROM #list ll ORDER BY GroupID, accountid FOR XML PATH('row'), TYPE, ROOT('root')).query('

No.

Group ID

Account ID

Country

Account Transaction Sum

Total Transaction Sum

{$pos}

{data($row/AccountID)}

{data($row/Country)}

{data($row/AccountTransactionSum)}

{$pos}

{data($row/GroupID)}

{data($row/AccountID)}

{data($row/Country)}

{data($row/AccountTransactionSum)}

{data($row/TotalTransactionSum)}

{$pos}

{data($row/GroupID)}

{data($row/AccountID)}

{data($row/Country)}

{data($row/AccountTransactionSum)}

{data($row/TotalTransactionSum)}

全部回复(2)

P粉3109311982024-03-31 14:04:19 2楼

对于 Siggemannen 的非常好的答案，我只想添加一种替代方法来处理 xquery 中的那些 td，

定义 rowspan 和 display 后，您可以在 xquery for 循环中使用它们

for $row in /root/row
let $pos := count(root/row[. 
 {$pos}
 {data($row/GroupID)}
 {data($row/AccountID)}
 {data($row/Country)}
 {data($row/AccountTransactionSum)}
 {data($row/TotalTransactionSum)}

赞 +0

添加回复

P粉7544734682024-03-31 12:15:21 1楼

这是一个很好的问题，因为我不知道 xquery 可以发挥这种魔力！这就是我想到的：

{for $row in /root/row let $pos := count(root/row[. 0) then else if ($row/rowspan > 1) then else }

No.	Group ID	Account ID	Country	Account Transaction Sum	Total Transaction Sum
{$pos}	{data($row/AccountID)}	{data($row/Country)}	{data($row/AccountTransactionSum)}
{$pos}	{data($row/GroupID)}	{data($row/AccountID)}	{data($row/Country)}	{data($row/AccountTransactionSum)}	{data($row/TotalTransactionSum)}
{$pos}	{data($row/GroupID)}	{data($row/AccountID)}	{data($row/Country)}	{data($row/AccountTransactionSum)}	{data($row/TotalTransactionSum)}

')); SELECT @xmlBody

基本上我创建了两列，rowspan 和skipTd。第一个控制是否应适用 rowspan，第二个表示是否应跳过当前 ，因为它属于同一组。

然后我向 xquery 添加了一个嵌套的 if ，因此它根据这两个标志返回 rowspanned、“跳过”或正常 HTML。也许有更好的方法，我不是专家。