幸运的是,在这种情况下,我们可以简单地将子查询替换为联接并直接选择值:
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