在SQL Server 2014中组合两个按汇总分组查询的小计

Question

我有一个嵌套的统一查询，其结构如下，本质上是该解决方案中的两个查询结构：在sql server 2008 中使用具有列和行总数的透视表

是否可以在分组汇总查询之间组合两列小计行？

SELECT
[Line] = ISNULL(line_name, 'Total'),
[A] = SUM([A]),
[B] = SUM([B]),
[C] = SUM([C]),
Total = SUM([A]+[B]+[C])
FROM (
SELECT sys_qty, line_name, stage_name
FROM table1 a 
INNER JOIN table2 b 
ON a...=b...
INNER JOIN table3 c 
ON a...=c...
WHERE line_name LIKE '%pattern1%'
) s1
PIVOT (
COUNT(sys_qty)
FOR stage_name IN ([A],[B],[C])
) p1
GROUP BY
ROLLUP(line_name)
UNION ALL
SELECT
[Line] = ISNULL(line_name, 'Total'),
[A] = SUM([A]),
[B] = SUM([B]),
[C] = SUM([C]),
Total = SUM([A]+[B]+[C])
FROM (
SELECT sys_qty, line_name, stage_name
FROM table1 a 
INNER JOIN table2 b 
ON a...=b...
INNER JOIN table3 c 
ON a...=c...
INNER JOIN table4 d 
ON b...=d...
WHERE line_name LIKE '%pattern2%'
) s1
PIVOT (
COUNT(sys_qty)
FOR stage_name IN ([A],[B],[C])
) p2
GROUP BY
ROLLUP(line_name)
;

实际结果：

Cc+fhi

	A	B
p1.行1	a	b	c
p1.row2	d	e	f
阶段总计	a+d	b+e
第2行	g
阶段合计

似乎您希望在数据透视之前，在派生/嵌套的SELECT中UNION ALL，然后对其进行分组和数据透视。

话虽如此，使用COUNT(CASE可能比使用PIVOT更容易

SELECT
Line = CASE WHEN GROUPING(line_name) = 1 THEN 'Total' ELSE line_name END,  -- deals with nulls
A = COUNT(CASE WHEN stage_name = 'A' THEN 1 END),
B = COUNT(CASE WHEN stage_name = 'B' THEN 1 END),
C = COUNT(CASE WHEN stage_name = 'C' THEN 1 END),
Total = COUNT(*)
FROM (
SELECT line_name, stage_name
FROM table1 a 
INNER JOIN table2 b 
ON a...=b...
INNER JOIN table3 c 
ON a...=c...
WHERE line_name LIKE '%pattern1%'
UNION ALL
SELECT line_name, stage_name
FROM table1 a 
INNER JOIN table2 b 
ON a...=b...
INNER JOIN table3 c 
ON a...=c...
INNER JOIN table4 d 
ON b...=d...
WHERE line_name LIKE '%pattern2%'
) s
WHERE stage_name IN ('A', 'B', 'C')  -- do you need this also, to filter out other values?
-- ... it might improve perf, also needed to get COUNT(*) to work correctly
GROUP BY
ROLLUP(line_name)
;