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

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

e.g.我是甲班的1號

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

e.g.我是甲班的

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

--找出超過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