如何使用递归字符串连接构建包含小计的HTML表格？ [英] How to build an HTML table with subtotals using a recursive string concatenation?

问题描述

我有下两个表：

  CREATE TABLE #SalesByStore（
 Brand VARCHAR（10），
 StoreName VARCHAR（50），
销售小数（10,2）
）
 
 CREATE TABLE #SalesByBrand（
品牌VARCHAR（10），
 TotalSales DECIMAL（10,2）
）
  

我试图建立一个HTML使用递归字符串连接的表体，并且我需要显示按品牌排序的商店销售情况，以及来自同一品牌的每组商店显示该品牌的销售小计后，如下所示：

我是这样做的：

  DECLARE @tableBody NVARCHAR（MAX），@lastBrand VARCHAR（10）; 
 SELECT @ tableBody =''; 
 
 SELECT 
 @tableBody 
 = @tableBody 
 + CASE 
 WHERE @lastBrand IS NOT NULL AND @ lastBrand<> SS.Brand $ b $ （选择TOP 1 CAST（SB.TotalSales AS VARCHAR（15））FROM #SalesByBrand（< td colspan =2>小计< / td>< td>'
 + 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？）。

你能否以另一种方式在SQL Server 2005中创建带有小计的HTML表？

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

编辑：我已将案例从结尾移至开始，因为在新品牌组开始前必须绘制小计行。幸运的是，在这种情况下，我们可以简单地将您的子查询替换为一个连接，并直接选择该值：

  DECLARE @tableBody NVARCHAR（MAX），@lastBrand VARCHAR（10），@lastTotal decimal（10,2）; 
 SELECT @ tableBody =''; 
 
 SELECT 
 @tableBody 
 = @tableBody 
 + CASE 
 WHERE @lastBrand IS NOT NULL AND @ lastBrand<> SS.Brand 
 THEN'< tr>< td colspan =2>小计< / td>< td>'
 + CAST（@lastTotal AS VARCHAR（15）） - $ b +'< / 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  - 保存最后的总计
 FROM #SalesByStore SS 
加入#SalesByBrand SB上SS.Brand = SB.Brand  - 加入获得品牌总数
 ORDER BY SS.Brand 
 
  - 最后添加最后的总数
 SELECT 
 @tableBody 
 = @tableBody 
 +'< tr>< td colspan =2>小计< / td>< td> 
 + CAST（@lastTotal AS VARCHAR（15））
 +'< / td>< / tr>'
  

你当然想出了一个创建这个HTML表格的巧妙方法。请注意，我已经将事情做了一些改动，并一起解决了一个问题。

我测试了这组数据：

  insert into #SalesByStore选择'A'，'Store 1'，1000 
插入#SalesByStore选择'A'，'Store 2'，2000 
插入#SalesByStore选择'B'，'Store 3'，1500 
插入#SalesByStore选择'B'，'Store 4'，2100 
插入#SalesByStore选择'B'，'Store 5'，3100 
插入#SalesByBrand选择'A'，3000 
插入#SalesByBrand选择'B'，6700 
  

I have the next two tables:

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

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

I am trying to build an HTML table body using recursive string concatenation, and I need to show the sales by store ordered by brand, and after each group of stores from a same brand show the sales subtotals for that brand, like this:

I am doing it the following way:

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

The problem is that the sub-query that gets me the sub-total amount by brand always returns NULL because @lastBrand remains null for the sub-query (see this stack overflow question for an explain about why this happens: Why subquery inside recursive string concatenation always returns NULL?).

Can you suggest me another way to create the HTML table with subtotals in SQL Server 2005?

By the way, I need to build the HTML table in SQL Server in order to send it inside a db mail.

EDIT: I have moved the case from the ending to the beggining of the concatenation, because the subtotal row must be draw before the new brand group begins. Sorry for the mistake.

解决方案

Luckily in this case, we can simply swap out your subquery for a join and select the value directly:

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>'

You've certainly come up with a clever method of creating this HTML table. Note that I've moved things around a little and hacked together a solution.

I tested with this set of data:

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

这篇关于如何使用递归字符串连接构建包含小计的HTML表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！