在查询结果中插入行(总和) [英] Insert line into a query result (sum)

问题描述

我有一份报告显示客户订购的产品及其价格:

I have a report that shows products that customers have ordered, along with their prices:

CompanyA    Product 7    14.99  
CompanyA    Product 3    45.95
CompanyA    Product 4    12.00
CompanyB    Product 3    45.95
CompanyC    Product 7    14.99
CompanyC    Product 3    45.95

我想插入一行来汇总每个公司的订单，如下所示:

I'd like to insert a line that sums each company's order, like this:

CompanyA    Product 7    14.99  
CompanyA    Product 3    45.95
CompanyA    Product 4    12.00
               Total:    72.94
CompanyB    Product 3    45.95
               Total:    45.95
CompanyC    Product 7    14.99
CompanyC    Product 3    45.95
               Total:    60.94

这里有一些代码显示了我所拥有的查询的基本结构:

Here's some code that shows the basic structure of the query I have:

SELECT company
   , product
   , price
FROM companyMaster
ORDER BY company,
   , product,
   , price;

有人知道怎么做吗?我是在 Transact-SQL (Microsoft SQL Server) 中编写的.

Does anyone know how to do this? I'm writing this in Transact-SQL (Microsoft SQL Server).

推荐答案

感谢大家的反馈/帮助，这至少让我想到了不同的方法.我想出了一些不依赖于我使用的 SQL Server 版本的东西(我们的供应商经常更改版本，所以我必须尽可能地交叉兼容).

Thanks for everyone's feedback/help, it at least got me thinking of different approaches. I came up with something that doesn't depend on what version of SQL Server I'm using (our vendor changes versions often so I have to be as cross-compliant as possible).

这可能被认为是一种 hack(好吧，它是一种 hack)，但它可以工作，并且可以完成工作:

This might be considered a hack (ok, it is a hack) but it works, and it gets the job done:

SELECT company
   , product
   , price
FROM companyMaster
ORDER BY company,
   , product,
   , price

UNION

SELECT company + 'Total'
   , ''
   , SUM(price)
FROM companyMaster
GROUP BY company

ORDER BY company;

这个解决方案基本上使用了两个 select 语句的 UNION.第一个与原始完全相同，第二个产生我需要的总和线.为了正确定位总和行，我对公司名称进行了字符串连接(附加单词Total")，这样当我按公司名称的字母顺序排序时，Total 行将显示在每个公司部分的底部.

This solution basically uses the UNION of two select statements. The first is exactly like the orginal, the second produces the sum line I needed. In order to correctly locate the sum line, I did a string concatenation on the company name (appending the word 'Total'), so that when I sort alphabetically on company name, the Total row will show up at the bottom of each company section.

这是最终报告的样子(不完全是我想要的，但功能相当，只是看起来不太漂亮:

Here's what the final report looks like (not exactly what I wanted but functionally equivalent, just not very pretty to look at:

CompanyA    Product 7    14.99  
CompanyA    Product 3    45.95
CompanyA    Product 4    12.00
CompanyA Total           72.94
CompanyB    Product 3    45.95
CompanyB Total           45.95
CompanyC    Product 7    14.99
CompanyC    Product 3    45.95
CompanyC Total           60.94

这篇关于在查询结果中插入行(总和)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！