iT邦幫忙

0

sql 流水號 row_number VS. rank VS. dense_rank

  • 分享至 

  • xImage
  •  

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

CREATE TABLE PRGS
(
	CODE int identity(1,1),
	NAME VARCHAR(100)
)
--流水號 --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

e.g.學號
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

e.g.我是甲班的1號
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

e.g.我是甲班的
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 

Sybase IQ語法-流水號

方法1-IDENTITY

create table #tmp
(
f0 int IDENTITY
,f1 int
,f2 int
)
insert into #tmp(f1,f2) select 1,1
insert into #tmp(f1,f2) select 1,2
insert into #tmp(f1,f2) select 1,3
insert into #tmp(f1,f2) select 1,4

select * from #tmp

方法2-NUMBER

create table #tmp
(
f1 int
,f2 int
)
insert into #tmp select 1,1
insert into #tmp select 1,2
insert into #tmp select 1,3
insert into #tmp select 1,4
select ONO=NUMBER(*),* from #tmp

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


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

1
海綿寶寶
iT邦大神 1 級 ‧ 2017-11-21 21:00:50

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

圓頭人 iT邦研究生 5 級 ‧ 2017-11-22 17:35:30 檢舉

^^感謝提供如此神器

我要留言

立即登入留言