我正在使用此查詢建立 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
對於 Siggemannen 的非常好的答案，我只想添加一種替代方法來處理 xquery 中的那些 td，
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定義 rowspan 和 display 後，您可以在 xquery for 迴圈中使用它們
這是一個很好的問題，因為我不知道
xquery可以發揮這種魔力！ 這就是我想到的：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('基本上我創建了兩列，rowspan 和skipTd。第一個控制是否應適用 rowspan，第二個表示是否應跳過目前，因為它屬於同一組。
然後我向 xquery 添加了一個嵌套的 if ，因此它根據這兩個標誌返回 rowspanned、“跳過”或正常 HTML。也許有更好的方法，我不是專家。