在 SQL 中合并 HTML 表中的单元格

发布于02月25日

我正在使用此查询创建一个HTML表并通过邮箱发送它. 只有当Group ID和Total Transaction Sum列的值相同时，才能合并它们的单元格以提高可读性吗？下面是我想要得到的结果

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个

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('<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 if ($row/skipTd > 0) then <tr align="center" valign="center"> <td>{$pos}</td> <td>{data($row/AccountID)}</td> <td>{data($row/Country)}</td> <td>{data($row/AccountTransactionSum)}</td> </tr> else if ($row/rowspan > 1) then <tr align="center" valign="center"> <td>{$pos}</td> <td rowspan ="{data($row/rowspan)}">{data($row/GroupID)}</td> <td>{data($row/AccountID)}</td> <td>{data($row/Country)}</td> <td>{data($row/AccountTransactionSum)}</td> <td rowspan ="{data($row/rowspan)}">{data($row/TotalTransactionSum)}</td> </tr> else <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

在 SQL 中合并 HTML 表中的单元格

推荐答案

Html相关问答推荐

HTML横幅未正确对齐页面顶部

NG8004：找不到名为""的管道.'' [插件Angular 编译器]

Font Awesome 6插入符号未显示

为什么从输入步骤中的扣减不能按预期进行？

响应CSS中的两到三列布局

我怎样才能有一个div，在其中放置一个消息，打破和不go 的div？

SVG动画只有在打开S开发工具的浏览器时才开始播放

单独处理Button：Focus的多行按钮

更改Angular 为17的material Select 字段的高度？

从html文件中提取元素的Python BeautifulSoup

`table>；的消失；tbody：nth子级(1)`HTML

如何使活动选项卡背景作为父背景图像

视频重新加载而不是在 Swift 中暂停 WKWebView

如何检测输入字段是否没有必填且没有占位符？

当我将有序列表居中时，数字会跑到列表的另一行

两个span，一个在div居中，好像没有另一个，另一个在右边

如何根据行中的其中一列将行居中？

水平对齐两列中的两个按钮同一级别

每次按下按钮时减小字体大小的 jquery 函数

图标未定位到右上角