html - 如何使用递归字符串连接构建带有小计的 HTML 表？

Question

我有接下来的两个表:

CREATE TABLE #SalesByStore (
    Brand VARCHAR(10),
    StoreName VARCHAR(50),
    Sales DECIMAL(10,2)
)

CREATE TABLE #SalesByBrand (
    Brand VARCHAR(10),
    TotalSales DECIMAL(10,2)
)

我正在尝试使用递归字符串连接构建一个 HTML 表体，我需要显示按品牌排序的商店的销售额，并且在来自同一品牌的每组商店显示该品牌的销售小计之后，就像这样:

我的做法是:

DECLARE @tableBody NVARCHAR(MAX), @lastBrand VARCHAR(10);
SELECT @tableBody='';

SELECT
  @tableBody
    = @tableBody
      + CASE
          WHEN @lastBrand IS NOT NULL AND @lastBrand<>SS.Brand
            THEN '<tr><td colspan="2">Subtotal</td><td>'
                 + (SELECT TOP 1 CAST(SB.TotalSales AS VARCHAR(15)) FROM #SalesByBrand SB WHERE SB.Brand=@lastBrand)
                 + '</td></tr>'
            ELSE '' END
      + '<tr><td>' + SS.Brand + '</td><td>'
      + SS.StoreName + '</td><td>' + CAST(SS.Sales AS VARCHAR(15)) + '</td></tr>',
  @lastBrand = SS.Brand
FROM #SalesByStore SS
ORDER BY SS.Brand

问题是，按品牌获取小计金额的子查询总是返回 NULL，因为 @lastBrand 对于子查询仍然为 null(请参阅此堆栈溢出问题以解释为什么会发生这种情况:Why subquery inside recursive string concatenation always returns NULL?) .

你能建议我另一种在 SQL Server 2005 中创建带小计的 HTML 表的方法吗？

顺便说一下，我需要在 SQL Server 中构建 HTML 表，以便在数据库邮件中发送它。

编辑: 我已将案例从连接的结尾移到开头，因为必须在新品牌组开始之前绘制小计行。对不起，错误。

Answer 1

幸运的是，在这种情况下，我们可以简单地将您的子查询换成连接并直接选择值:

DECLARE @tableBody NVARCHAR(MAX), @lastBrand VARCHAR(10), @lastTotal decimal(10,2);
SELECT @tableBody='';

SELECT
  @tableBody
    = @tableBody
      + CASE
          WHEN @lastBrand IS NOT NULL AND @lastBrand<>SS.Brand
            THEN '<tr><td colspan="2">Subtotal</td><td>'
                 + CAST(@lastTotal AS VARCHAR(15)) -- Add the last total
                 + '</td></tr>'
            ELSE '' END
      + '<tr><td>' + SS.Brand + '</td><td>'
      + SS.StoreName + '</td><td>' + CAST(SS.Sales AS VARCHAR(15)) + '</td></tr>',
  @lastBrand = SS.Brand,
  @lastTotal = SB.TotalSales -- Save the last total, too
FROM #SalesByStore SS
    join #SalesByBrand SB on SS.Brand = SB.Brand -- Join to get brand totals
ORDER BY SS.Brand

-- Finally add the last total
SELECT
  @tableBody
    = @tableBody
      + '<tr><td colspan="2">Subtotal</td><td>'
      + CAST(@lastTotal AS VARCHAR(15))
      + '</td></tr>'

您肯定想出了一个创建此 HTML 表格的巧妙方法。请注意，我稍微移动了一些东西并拼凑了一个解决方案。

我测试了这组数据:

insert into #SalesByStore select 'A', 'Store 1', 1000
insert into #SalesByStore select 'A', 'Store 2', 2000
insert into #SalesByStore select 'B', 'Store 3', 1500
insert into #SalesByStore select 'B', 'Store 4', 2100
insert into #SalesByStore select 'B', 'Store 5', 3100
insert into #SalesByBrand select 'A', 3000
insert into #SalesByBrand select 'B', 6700