在表中的一些連續行之后插入一行的最佳做法是什么？

我在表格中有以下資料：

REGION,CATEGORY,SUBCATEGORY,ItemName,ItemViews,sales,ItemViewspercentage
CE,Infotainment,Safe Exit Assist,Voice Commands,5,4,125.00%
EA,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
SO,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
WE,Infotainment,Safe Exit Assist,Voice Commands,5,5,100.00%
CE,Infotainment,Safe Exit Assist,Voice Recognition Tips,2,2,100.00%
EA,Infotainment,Safe Exit Assist,Voice Recognition Tips,1,1,100.00%
SO,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,2,150.00%
SW,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
WE,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
EA,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SO,Infotainment,Voice Recognition,Setting Clock,1,1,100.00%
WE,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SW,Infotainment,Voice Recognition,Setting Presets,2,1,200.00%
CE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
WE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
CE,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
CE,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
EA,KIA Drive Wise,Android Auto,Driver Attention Warning,2,3,66.67%
SO,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
SW,KIA Drive Wise,Android Auto,Driver Attention Warning,1,1,100.00%
WE,KIA Drive Wise,Android Auto,Driver Attention Warning,5,5,100.00%

我希望上面的資料是這樣的：

REGION,CATEGORY,SUBCATEGORY,ItemName,ItemViews,sales,ItemViewspercentage
CE,Infotainment,Safe Exit Assist,Voice Commands,5,4,125.00%
EA,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
SO,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
WE,Infotainment,Safe Exit Assist,Voice Commands,5,5,100.00%
Total,Infotainment,Safe Exit Assist,Voice Commands,14,11,78.57%
CE,Infotainment,Safe Exit Assist,Voice Recognition Tips,2,2,100.00%
EA,Infotainment,Safe Exit Assist,Voice Recognition Tips,1,1,100.00%
SO,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,2,150.00%
SW,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
WE,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
Total,Infotainment,Safe Exit Assist,Voice Recognition Tips,12,11,109.09%
EA,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SO,Infotainment,Voice Recognition,Setting Clock,1,1,100.00%
WE,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
Total,Infotainment,Voice Recognition,Setting Clock,7,3,233.33%
SW,Infotainment,Voice Recognition,Setting Presets,2,1,200.00%
CE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
WE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
Total,Infotainment,Voice Recognition,Sound Settings,2,2,100.00%
CE,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
Total,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
CE,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
EA,KIA Drive Wise,Android Auto,Driver Attention Warning,2,3,66.67%
SO,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
SW,KIA Drive Wise,Android Auto,Driver Attention Warning,1,1,100.00%
WE,KIA Drive Wise,Android Auto,Driver Attention Warning,5,5,100.00%
Total,KIA Drive Wise,Android Auto,Driver Attention Warning,16,15,106.66%

我想在特定數量的行之后插入一行。我應該使用 SQL Server 中的哪個回圈來完成我的作業？

uj5u.com熱心網友回復：

你可以用GROUPING SETS這個

SELECT
  REGION = CASE WHEN GROUPING(REGION) = 1 THEN 'Total' ELSE REGION END,
  CATEGORY,
  SUBCATEGORY,
  ItemName,
  ItemViews = SUM(ItemViews),
  sales = SUM(sales),
  ItemViewspercentage = FORMAT(SUM(ItemViews) * 1.0 / SUM(sales), 'P2')
FROM YourTable t
GROUP BY GROUPING SETS (
  (CATEGORY, SUBCATEGORY, ItemName, REGION),
  (CATEGORY, SUBCATEGORY, ItemName)
);

db<>小提琴

標籤：sql sql服务器 sql-server-2008

上一篇：nodejs：檢測檔案中相同字串的多次出現

下一篇：自定義UIView的子視圖何時知道自己的邊界？