添加跳过某些列的Total Sum列

Question

我有一个表，其中对于某些客户，有一个代码表示它是谁，然后是客户的全名，然后是他们完成的总销售额与总收入。

tbody> <<tr>

客户代码	客户名称	总销售额	总收益
abc	def	2	11
ghi	. jkl	1	20
mno	pqr	7	6
stu	vwx	5	9

正如其他人指出的那样，这通常是在表示层解决的问题，但是，您可以使用WITH ROLLUP来实现结果集:

DECLARE @Sales TABLE (CustomerCode NVARCHAR(3), CustomerName NVARCHAR(3), TotalSales INT, TotalRevenue INT)
INSERT INTO @Sales (CustomerCode, CustomerName, TotalSales, TotalRevenue) VALUES
('abc', 'def', 2, 11),
('ghi', 'jkl', 1, 20),
('mno', 'pqr', 7, 6 ),
('stu', 'vwx', 5, 9 )
SELECT CustomerCode, COALESCE(CustomerName,'TOTAL') AS CustomerName, TotalSales, TotalRevenue
FROM (
SELECT CustomerCode, CustomerName, SUM(TotalSales) AS TotalSales, SUM(TotalRevenue) AS TotalRevenue
FROM @Sales
GROUP BY CustomerCode, CustomerName WITH ROLLUP
) a
WHERE a.CustomerName IS NOT NULL
OR (
a.CustomerCode IS NULL 
AND a.CustomerName IS NULL
)

CustomerCode    CustomerName    TotalSales  TotalRevenue
--------------------------------------------------------
abc             def             2           11
ghi             jkl             1           20
mno             pqr             7           6
stu             vwx             5           9
NULL            TOTAL           15          46