sql 小計合計排序

很久沒寫東西了!

學校	班級	學號	姓名	分數
學校A	班級01	01	小林	90
學校A	班級01	02	小李	60
	班級小計	人數	2	150
學校A	班級02	01	小強	75
	班級小計	人數	1	75
	學校合計	人數	3	225
學校B	班級01	01	小小	85
	班級小計	人數	1	85
	學校合計	人數	1	85
	全縣合計	學校數:2	學生數:4	總分:305

像這樣的列表裡面出現小計和合計的情況在統計報表中是經常出現的, 在這些報表裡面, 怎麼排序就是一個難題了!

上面的表格, 學生的排序是按學號的大小排序的, 班級目前是也是使用大小排序, 但是學校不是大小排序, 而是固定排序, 對於這種情況, 那麼我們就需要自己手工給學校做一個序號, 方便他們排序了.

但是怎麼把"班級小計","學校合計"和"全縣合計" 插入到正確的位置呢?

我們正常的排序是:     order by  學校, 班級, 學號

• "班級小計" 是要放在某個班級的最後一位學生後面, 最後一位是號碼是什麼, 我們可以求出來, 但是這樣寫起來每次都要進行一次最後一位的判斷! 這樣顯然是低效和繁瑣的.
• "學校合計" 是要放在某個學校的班級資訊的最後一位.
• "全縣合計" 是要放在某個縣的學校資訊的最後一位.

我們可以從中看出, "最後一位" 這個是關鍵, 怎麼確定最後一位呢?  老師可以幫我們安排學位, 為什麼不能幫我們的小計直接安排在最後呢? 學校可以安排班級順位, 那由它直接安排合計在最後不就行了嗎? 所以

• "班級小計" order by 學校,班級
• "學校合計" order by 學校

由統計的範圍來決定小計合計的排序位置, 簡單來說,

" 學校A ->班級01->班級合計列"  直接賦予"班級010" 這樣的資訊,  這樣通過order by 學校,班級 , 班級01 的"班級小記" 就會在該班級中排最後(當然不是說一定在後面加"0"的, 具體要加什麼可以由自己決定, 可以參考一些排序優先順序的,如果是oracle, null 預設是最大值, 要注意不要讓班級01的班級小記掉到其他班級那裡去了 ).

" 學校A->學校合計" 直接賦予"學校A0" 這樣的資訊, 理由同上.

 

學校	班級	縣層面排序值 (自訂的)	學校排序值	班級排序值	學號	姓名	分數
學校A	班級01	01	學校A	班級01	01	小林	90
學校A	班級01	01	學校A	班級01	02	小李	60
	班級小計	01	學校A	班級010	人數	2	150
學校A	班級02	01	學校A	班級02	01	小強	75
	班級小計	01	學校A	班級020	人數	1	75
	學校合計	01	學校A0		人數	3	225
學校B	班級01	02	學校B	班級01	01	小小	85
	班級小計	02	學校B	班級010	人數	1	85
	學校合計	02	學校B0		人數	1	85
	全縣合計	99			學校數:2	學生數:4	總分:305

注意裡面的 藍色字型,    這樣就簡單了

   order 縣層面排序值,學校排序值,班級排序值, 學號

一般思路:

select 調整顯示內容

from (

      select * from

       (select 學生資訊,學號,班級,學校, 縣 as 縣層面排序值, 學校 as 學校排序值, 班級 as 班級排序值, 類型標識(用於區分資料的層次)

       union all

       select 班級小計(count(學號), sum(分數)), null 班級排序值,.............

           group by 縣級,學校,班級

       union all

       select 學校合計(count(學號), sum(分數)), null 學校排序值,.............

            group by 縣級,學校

       union all

       select 全縣合計(count(學號), sum(分數)), null 縣層面排序值,.............)

            group by 縣級

       order 縣層面排序值,學校排序值,班級排序值, 學號

)

當然這些在資料量不大的情況還行, 如果資料量大的話, 各種處理方式, 最好把統計的資料緩衝起來, 不要重複執行sum, count 這些耗時的操作.

--------------------------------------可愛的分割線---------------------------------

像上面這樣的統計, 還有更簡單的做法, 那就是使用 group by rollup(學校, 班級,(學號,姓名)),或者用group by rollup(學校,班級,學號),姓名欄位用max(姓名) as 姓名

學校	班級	學號	姓名	分數(sum)	人數(count)	grouping_id(學校,班級,學號)
學校A	班級01	01	小林	90	1	0
學校A	班級01	02	小李	60	1	0
學校A	班級02	01	小強	75		0
學校B	班級01	01	小小	85		0
學校A	班級01			150	2	1
學校A	班級02			75	1	1
學校B	班級01			85	1	1
學校A				225	3	3
學校B				85	1	3
				305	4	7

在前面說過了, oracle 排序中null 預設是最大值, 所以如果是升序, 直接order by  學校, 班級, 學號 就行了, 很方便吧.

grouping_id() 方法是配合group by rollup 使用的, 這裡我們用來區分資料層次, 我們可以看到0,1,3,7 這樣的數字, 可能不好理解,

但是轉為二進位就好理解了, 111 =7   011=3  001=1 000=0,      簡單來說, 統計的範圍越大, 數字越大,

group by rollup(學校, 班級,(學號,姓名))

 分解成       group by 學校, 班級, (學號,姓名) + group by 學校, 班級 + group by 學校 + group by null

grouping_id(學校,班級,學號)         000          001     011      111

一般思路:

select 調整顯示內容

from (

        select 學校, 班級, 學號, 姓名, sum(分數) 分數, count(學號) 學號, grouping_id(學校,班級,學號)  gp_num

        ...................... 

        group by rollup(學校, 班級,(學號,姓名))

        order by  學校, 班級, 學號

)

這種寫法跟上面的寫法, 效率差距不大, 但是簡潔很多, 當然不同的資料庫對應null 的排序優先順序是不一致的, 用第一種方法通用性強一點, 但是寫起來代碼很長.