將字元插入整行並與 row_number 的 continue 聯合

我有第一個查詢

IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
select 
   IDX=ROW_NUMBER() over(order by A_ID)
   ,A = A_ID
   ,Number = COUNT(*)
   ,Value = sum(ammount)
into #tmp1
from #t
group by A_ID

需要與

  select   
   IDX  = ''
   ,A = 'Total'
   ,SUM (Number)
   ,SUM(Value)
  from #tmp1

在 UNION ALL 之後，我得到數據集：

+--------+------------+-------------+--------+
|  IDX   |     A      |  Number     |  Value |
+--------+------------+-------------+--------+
|      1 |  PCX       |      12     |  22.00 |
|      2 |  TOC       |       3     |  89.00 |
|      0 | Total      |      15     | 111.00 |
+--------+------------+-------------+--------+

但是我需要：

   +--------+------------+-------------+--------+
   |  IDX   |     A      |  Number     |  Value |
   +--------+------------+-------------+--------+
   |      1 |  PCX       |      12     |  22.00 |
   |      2 |  TOC       |       3     |  89.00 |
   |============================================|
   |      3 | Total      |      15     | 111.00 |
   +--------+------------+-------------+--------+

如何插入整行 ‘=’ 並繼續 IDX 的行號？

版本是 2014 企業版。

您可以使用GROUP BY GROUPING SETS和GROUPING()功能：

SELECT 
   idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id),
   a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END, 
   number = COUNT(*),
   value = SUM(amount)
FROM #t
GROUP BY GROUPING SETS ((a_id), ()) 
ORDER BY idx ; 

在舊版本（沒有GROUPING SETS）中，您可以ROLLUP改用。在更舊的版本（或任何版本，如果您更喜歡更簡單的查詢）中，您可以將查詢拆分為 2 個部分，並UNION ALL使用：

SELECT 
   idx = ROW_NUMBER() OVER (a_id),
   a = a_id, 
   number = COUNT(*),
   value = SUM(amount)
FROM #t
GROUP BY a_id

UNION ALL

SELECT 
   1 + COUNT(DISTINCT a_id),
   a_id, 
   COUNT(*),
   SUM(amount)
FROM #t

ORDER BY idx ; 

在rextester.com測試

引用自：https://dba.stackexchange.com/questions/158237