~楓花雪岳~: [SQL] 次序函數應用-資料分組

星期六, 7月 28, 2012

[SQL] 次序函數應用-資料分組

此論壇問題是要針對資料進行分組，分組模式有兩種

從 1 數到 3，不斷循環。
每 3 筆一個群組。

根據提問者提供資料，利用 ROW_NUMBER() 搭配除法（/）和模除（%）來進行處理

DECLARE @Temp TABLE ([Name] char(10))
INSERT INTO @Temp VALUES('A')
INSERT INTO @Temp VALUES('E')
INSERT INTO @Temp VALUES('C')
INSERT INTO @Temp VALUES('H')
INSERT INTO @Temp VALUES('G')
INSERT INTO @Temp VALUES('D')
INSERT INTO @Temp VALUES('B')
INSERT INTO @Temp VALUES('I')
INSERT INTO @Temp VALUES('J')
INSERT INTO @Temp VALUES('F')

SELECT 
  [Name] , 
  -- ROW_NUMBER() 產生的號碼排序
  ROW_NUMBER() OVER (ORDER BY [Name]) AS ROWNO ,
  -- 分組模式 1
  ((ROW_NUMBER() OVER (ORDER BY [Name]) - 1) % 3 + 1) AS Count3 ,
  -- 分組模式 2
  ((ROW_NUMBER() OVER (ORDER BY [Name]) - 1) / 3) + 1 AS Group3 
FROM @Temp

邏輯說明

ROW_NUMBER() 跑出號碼排序後，利用模除（%）取餘數，可以達到分組模式 1；利用除法（/）取商數可以達到分組模式 2，因為 ROW_NUMBER() 回傳值的資料型態為 bigint，整數除整數一定會得到整數，EX：10 / 3 會得到 3，不會是 3.333333，另外分組模式 1 和 2 中，-1 和 +1 的數學邏輯處理，是為了讓數字呈現從 1 開始顯示，閱讀上更直覺。