? ? ? ? ? ? ? ? ? ? ? ? ? ?計(jì)算某列的中間值

需求描述

需求：計(jì)算EMP表按照deptno分組，找出排在中間的SAL

解決方法:通過窗口函數(shù)集合COUNT（*）通過序號(hào)取模來(lái)判斷.

注: 數(shù)據(jù)庫(kù)數(shù)據(jù)集SQL腳本詳見如下鏈接地址

員工表結(jié)構(gòu)和數(shù)據(jù)初始化SQL腳本

SQL代碼

-- SQL Server： SELECT B.deptno,A.sal middle_Sal,B.row_CN,CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END Middle FROM ( SELECT empno,sal,deptno,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal) AS RN FROM emp)A JOIN(SELECT deptno,COUNT(*) AS row_CN FROM empGROUP BY deptno)B ON A.deptno = B.deptno AND A.RN = CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END

執(zhí)行結(jié)果

-- 同理我們可以在mysql實(shí)現(xiàn)： SELECT B.deptno,A.SAL,B.row_CN,ROUND(CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END) AS Middle FROM ( SELECT heyf_tmp.deptno,heyf_tmp.empno,heyf_tmp.sal,@rownum := @rownum+1 AS rn , IF(@pdept= heyf_tmp.deptno,@rank:=@rank +1, @rank:= 1) AS rank, @pdept:=heyf_tmp.deptno AS pre_deptno FROM ( SELECT A.deptno,A.sal,A.empno FROM emp AORDER BY deptno,sal ) heyf_tmp ,(SELECT @rownum :=0 , @pdept := NULL ,@rank := 0) a )A JOIN ( SELECT deptno,COUNT(*) AS row_CN FROM empGROUP BY deptno ) B ON A.deptno = B.deptno AND A.RANK = CASE WHEN (B.row_CN%2=0) THEN B.row_CN/2 ELSE (row_CN+1)/2 END

總結(jié)

以上是生活随笔為你收集整理的窗口分析函数_8_计算某列的中间值的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。