将行插入查询结果(总和) [英] 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).

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

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"后面加上)，以便当我按字母顺序对公司名称进行排序时，总计"行将显示在每个公司部分的底部.

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屋！