iT邦幫忙

0

sql 流水號 row_number VS. rank VS. dense_rank

Ref https://dotblogs.com.tw/kenny0721/2012/09/16/74823

--流水號 --identity(INT,1,1)
--準備data
drop table #Emp
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep 
INTO #Emp
UNION SELECT '002' As EmpId,'王小明' As Name,'HR' As Dep 
UNION SELECT '003' As EmpId,'王小明' As Name,'MIS' As Dep 
UNION SELECT '004' As EmpId,'王小明' As Name,'MIS' As Dep 
UNION SELECT '005' As EmpId,'王小明' As Name,'Fin' As Dep 
UNION SELECT '006' As EmpId,'王小明' As Name,'Fin' As Dep 
UNION SELECT '007' As EmpId,'王小明' As Name,'Fin' As Dep 

--按EmpID來編碼.公司裡的1號,公司裡的2號
SELECT row_number() OVER(ORDER BY EmpId) As Seq,* FROM #Emp Order BY EmpId

https://ithelp.ithome.com.tw/upload/images/20171120/20106764Xbos0UKLaG.png

--依部門來編碼.HR中部門的1號.HR中部門的2號
SELECT row_number() OVER(PARTITION BY  Dep ORDER BY EmpId) As Seq,* FROM #Emp Order BY EmpId

https://ithelp.ithome.com.tw/upload/images/20171120/20106764ToDWPiQqPa.png

--依Dep來分群,不跳號
select DENSE_RANK()  OVER(ORDER BY Dep) as Seq,* from #Emp order by Dep

https://ithelp.ithome.com.tw/upload/images/20171120/20106764Hde2nMNPQs.png

--依Dep來分群,跳號
select RANK() OVER(ORDER BY Dep) as Seq,* from #Emp order by Dep

https://ithelp.ithome.com.tw/upload/images/20171120/20106764TbPq2vt2KH.png

--找出超過3個人的部門
select * from 
(
	SELECT RowID = row_number() OVER(PARTITION BY  Dep ORDER BY EmpId)  ,* FROM #Emp 
) a
where RowID>=3 
Order BY EmpId
select Dep from 
(
	SELECT RowID = row_number() OVER(PARTITION BY  Dep ORDER BY EmpId)  ,* FROM #Emp 
) a
where RowID>=3
group by Dep 

https://ithelp.ithome.com.tw/upload/images/20171120/20106764uhm9hJUwuF.png


1 則留言

0
海綿寶寶
iT邦超人 1 級 ‧ 2017-11-21 21:00:50

中文姓名產生器
可以讓你的資料看起來比較實際一點

i58540041 iT邦新手 5 級 ‧ 2017-11-22 17:35:30 檢舉

^^感謝提供如此神器

我要留言

立即登入留言